class User(UserMixin):
    __dbfields = ["id", "email", "can_analyze", "full_name", "affiliation", "is_active"]

    def __init__(self, email, rid, can_analyze, full_name, affiliation, is_active):
        self.email = email
        self.rid = rid
        self.can_analyze = can_analyze
        self.full_name = full_name
        self.affiliation = affiliation
        self._is_active = is_active

    def get_id(self):
        return self.email

    def can_view_job(self, job_id, db):
        if self.is_admin():
            return True
        cur = db.cursor(MySQLdb.cursors.DictCursor)
        cur.execute("SELECT user_id FROM jobs WHERE user_id=%s and job_id=%s", (self.rid, job_id,))
        if cur.rowcount > 0:
            return True
        return False

    def is_admin(self):
        return self.email in current_app.config.get("ADMIN_USERS",[]) 

    def is_active(self):
        return self._is_active

    def log_login(self, db):
        cur = db.cursor(MySQLdb.cursors.DictCursor)
        sql = "UPDATE users SET last_login_date = NOW() WHERE id = %s"
        cur.execute(sql, (self.rid, ))
        db.commit()

    def get_collaborator(self, rid, db=None):
        where = WhereAll(
            WhereExpression("users.id in (select user_id from job_users where job_id in " +
              "(select job_id from job_users where user_id=%s and role_id=1) and user_id != %s)", (self.rid, self.rid)),
            WhereExpression("users.id = %s", (rid ,))
        )
        return User.__get_by_sql_where(db, where)

    def get_collaborators(self, query, db=None):
        where = WhereAll(
            WhereExpression("job_users.job_id in (select job_id from job_users where user_id=%s and role_id=1)", (self.rid,)),
            WhereExpression("job_users.user_id != %s", (self.rid,))
        )
        cols = User.__default_cols()
        group_by = [str(i+1) for i, x in enumerate(cols)]
        cols["count"] = "count(job_users.job_id)"
        qcols = User.__default_qcols()
        sqlcmd = User.__build_sql_command(cols, qcols, where, query)
        sqlcmd.set_group_by(group_by)
        sqlcmd.add_join(TableJoin("job_users", "job_users.user_id = users.id"))
        return User.__list_by_sql_command(db, sqlcmd)

    def get_collaborations(self, query, db=None):
        if db is None:
            db = sql_pool.get_conn()
        cur = db.cursor(MySQLdb.cursors.DictCursor)
        cols = OrderedDict([("user_id", "job_users.user_id"),
            ("user_email", "users.email"),
            ("user_full_name", "users.full_name"),
            ("user_affiliation", "users.affiliation"),
            ("job_id", "bin_to_uuid(jobs.id)"),
            ("job_name", "jobs.name"),
            ("job_creation_date", "DATE_FORMAT(jobs.creation_date, '%%Y-%%m-%%d %%H:%%i:%%s')"),
            ("creation_date", "DATE_FORMAT(users.creation_date, '%%Y-%%m-%%d %%H:%%i:%%s')"),
            ("role_id", "job_users.role_id")
        ])
        qcols = ["user_id", "user_email", "user_full_name", "user_affiliation",
            "job_id", "jon_name", "job_creation_date", "creation_date"]
        page, order_by, qsearch = SelectQuery.translate_query(query, cols, qcols)
        if not order_by:
            order_by = OrderClause(OrderExpression(cols["creation_date"], "DESC"))
        where = WhereAll(
            WhereExpression("job_users.job_id in (select job_id from job_users where user_id=%s)", (self.rid,)),
            WhereExpression("job_users.user_id != %s", (self.rid,))
        )
        sqlcmd = (SelectQuery()
            .set_cols([ "{} AS {}".format(v,k) for k,v in cols.items()])
            .set_table("job_users")
            .add_join(TableJoin("users", "job_users.user_id = users.id"))
            .add_join(TableJoin("jobs", "job_users.job_id = jobs.id"))
            .set_search(qsearch)
            .set_where(where)
            .set_order_by(order_by)
            .set_page(page))
        return PagedResult.execute_select(db, sqlcmd)

    @staticmethod
    def from_email(email, db):
        where = WhereExpression("email=%s", (email, ))
        return User.__get_by_sql_where(db, where)

    @staticmethod
    def from_id(rid, db = None):
        if db is None:
            db = sql_pool.get_conn()
        where = WhereExpression("id=%s", (rid, ))
        return User.__get_by_sql_where(db, where)

    @staticmethod
    def from_session_key(sess_key, db=None):
        if sess_key not in session:
            return None
        if db is None:
            db = sql_pool.get_conn()
        return User.from_email(session[sess_key], db)

    @staticmethod
    def list_all(config=None, query=None):
        db = sql_pool.get_conn()
        return User.__list_by_sql_where_query(db, query=query)

    @staticmethod
    def __get_by_sql_where(db, where=None):
        sqlcmd = User.__build_default_sql_command(where)
        return User.__get_by_sql_command(db, sqlcmd)

    @staticmethod
    def __get_by_sql_command(db, sqlcmd):
        if db is None:
            db = sql_pool.get_conn()
        results = User.__list_by_sql_command(db, sqlcmd=sqlcmd).results
        if len(results)!=1:
            return None
        res = results[0]
        return User(res["email"], res["id"], res["can_analyze"],
            res["full_name"], res["affiliation"], res["is_active"])

    @staticmethod
    def __default_cols():
        return OrderedDict([("id", "users.id"),
            ("email", "users.email"),
            ("full_name", "users.full_name"),
            ("affiliation", "users.affiliation"),
            ("can_analyze", "users.can_analyze"),
            ("creation_date", "DATE_FORMAT(users.creation_date, '%%Y-%%m-%%d %%H:%%i:%%s')"),
            ("last_login_date", "DATE_FORMAT(users.last_login_date, '%%Y-%%m-%%d %%H:%%i:%%s')"),
            ("is_active", "users.is_active")
        ])

    def __default_qcols():
        return ["id", "email", "full_name", "affiliation", "creation_date", "last_login_date"]

    @staticmethod
    def __build_default_sql_command(where=None, query=None):
        return User.__build_sql_command(
            User.__default_cols(),
            User.__default_qcols(),
            where, query)

    @staticmethod
    def __build_sql_command(cols, qcols=None, where=None, query=None):
        page, order_by, qsearch = SelectQuery.translate_query(query, cols, qcols)
        if not order_by:
            order_by = OrderClause(OrderExpression(cols["creation_date"], "DESC"))
        sqlcmd = (SelectQuery()
            .set_cols([ "{} AS {}".format(v,k) for k,v in cols.items()])
            .set_table("users")
            .set_where(where)
            .set_search(qsearch)
            .set_order_by(order_by)
            .set_page(page))
        return sqlcmd

    @staticmethod
    def __list_by_sql_where(db, where="", vals=()):
        result = User.__list_by_sql_where_query(db, where=WhereExpression(where, vals), query=None)
        return result.results

    @staticmethod
    def __list_by_sql_where_query(db, where=None, query=None):
        sqlcmd = User.__build_default_sql_command(where, query)
        return User.__list_by_sql_command(db, sqlcmd)

    @staticmethod
    def __list_by_sql_command(db, sqlcmd):
        if db is None:
            db = sql_pool.get_conn()
        return PagedResult.execute_select(db, sqlcmd)

    @staticmethod
    def create(new_values, db=None):
        updateable_fields = [x for x in User.__dbfields if x != "id"]
        fields = list(new_values.keys()) 
        values = list(new_values.values())
        bad_fields = [x for x in fields if x not in updateable_fields]
        if db is None:
            db = sql_pool.get_conn()
        if len(bad_fields)>0:
            raise Exception("Invalid update field: {}".format(", ".join(bad_fields)))
        sql = "INSERT INTO users (" + \
            ", ".join(fields)+ \
            ") values (" + \
            ", ".join(["%s"] * len(values)) + \
            ")"
        cur = db.cursor(MySQLdb.cursors.DictCursor)
        cur.execute(sql, values)
        db.commit()
        new_id = cur.lastrowid
        new_user = User.from_id(new_id, db=db)
        result = {"user": new_user}
        return result

    @staticmethod
    def update(user_id, new_values):
        updateable_fields = [x for x in User.__dbfields if x != "id"]
        fields = list(new_values.keys())
        values = [None if x=="" else x for x in new_values.values()]
        bad_fields = [x for x in fields if x not in updateable_fields]
        if len(bad_fields)>0:
            raise Exception("Invalid update field: {}".format(", ".join(bad_fields)))
        sql = "UPDATE users SET "+ \
            ", ".join(("{}=%s".format(k) for k in fields)) + \
            " WHERE id = %s"
        db = sql_pool.get_conn()
        cur = db.cursor()
        cur.execute(sql, values + [int(user_id)])
        db.commit()

    def as_object(self):
        obj = {key: getattr(self, key) for key in self.__dbfields if hasattr(self, key)} 
        obj["id"] = self.rid
        obj["is_active"] = self.is_active()
        return obj

    @staticmethod
    def counts(by=None, filters=None, config=None):
        join_jobs = False
        count = "COUNT(DISTINCT users.id)"
        if not by:
            by = []
        elif isinstance(by, str):
            by = by.split(",")
        if not filters:
            filters = []
        elif isinstance(filters, str):
            filters = filters.split(",")
        select = []
        group_by = []
        columns = []
        wheres = []
        for field in by:
            if field=="creation-month":
                select += [ "DATE_FORMAT(users.creation_date, '%Y-%m') as month"]
                group_by += [ "DATE_FORMAT(users.creation_date, '%Y-%m')"]
                columns += ["month"]
            elif field == "creation-year":
                select += ["year(users.creation_date) as year"]
                group_by += ["year(users.creation_date)"]
                columns += ["year"]
            elif field == "job-month":
                select += [ "DATE_FORMAT(jobs.creation_date, '%Y-%m') as month"]
                group_by += [ "DATE_FORMAT(jobs.creation_date, '%Y-%m')"]
                columns += ["month"]
                join_jobs = True
            elif field == "job-year":
                select += ["year(jobs.creation_date) as year"]
                group_by += ["year(jobs.creation_date)"]
                columns += ["year"]
                join_jobs = True
            else:
                raise Exception("Unrecognized field: {}".format(field))
        for filt in filters:
            if filt == "can-analyze":
                wheres += ["users.can_analyze = 1"]
            elif filt == "active":
                wheres += ["users.is_active = 1"]
            elif filt == "has-logged-in":
                wheres += ["users.last_login_date is not null"]
            elif filt == "successful":
                wheres += ["jobs.status_id = (select id from statuses where name = 'succeeded')"]
                join_jobs = True
            else:
                raise Exception("Unrecognized filter: {}".format(filt))
        select += [count + " as count"]
        columns += ["count"]
        sql = "SELECT " + ", ".join(select)
        sql += " FROM users"
        if join_jobs:
            sql += " JOIN jobs on users.id=jobs.user_id"
        if len(wheres):
            sql += " WHERE (" + "), (".join(wheres) + ")"
        if len(group_by):
            sql += " GROUP BY " + ", ".join(group_by)

        db = sql_pool.get_conn()
        cur = db.cursor(MySQLdb.cursors.DictCursor)
        cur.execute(sql)
        results = cur.fetchall()
        return {"header": {"columns": columns}, "data": results} class Job:
    __dbfields = ["user_id", "name", "description", "error_message", "status_id",
        "creation_date", "modified_date", "is_active"]
    __extfields = ["status", "role"]

    def __init__(self, job_id, meta=None, config=None):
        self.job_id = job_id
        for x in self.__dbfields + self.__extfields:
            setattr(self, x, None)
        self.root_path = "" 
        self.users = []
        self.meta = meta
        self.config = config

    def get_adjusted_phenotypes(self):
        phe_file = self.relative_path("output.phe")
        phenos = {}
        if os.path.exists(phe_file):
            with open(phe_file) as f:
                for line in f:
                    (sample, val) = line.split()
                    phenos[sample] = float(val)
        else:
            model = self.get_model(self.config)
            model_spec = self.meta
            geno = model.get_geno(model_spec)

            pheno = model.get_pheno(model_spec)
            phenos = model.get_response_values(model_spec, geno, pheno)
        return phenos

    def relative_path(self, *args):
        return os.path.expanduser(os.path.join(self.root_path, *args))

    def get_genotype_id(self):
        return self.meta.get("genotype", None) 

    def get_phenotype_id(self):
        return self.meta.get("phenotype", None)

    def get_model(self, config=None):
        return ModelFactory.get(self.meta.get("type", None), self.root_path, config)

    def get_output_files(self):
        files = []
        def add_if_exists(rel_path, display_name, primary=False):
            file_path = self.relative_path(rel_path)
            if os.path.exists(file_path):
                files.append({"path": rel_path, "size": os.path.getsize(file_path), 
                    "name": display_name, "primary": primary})
        add_if_exists("output.epacts.gz", "Epacts Results", True)
        add_if_exists("results.txt.gz", "SAIGE Results", True)
        add_if_exists("output.filtered.001.gz", "Filtered Results (p-val<0.001)")
        return files

    def get_output_primary_file(self):
        files = [x for x in self.get_output_files() if x.get("primary", False)]
        if len(files)==1:
            return files[0]["path"]
        else:
            return None

    def get_output_file_path(self):
        file_name = self.get_output_primary_file()
        if file_name:
            return self.relative_path(file_name)
        else:
            return None

    def get_owner(self):
        return User.from_id(self.user_id) 

    def get_param_hash(self):
        return Job.calc_param_hash(self.meta)

    def get_failure_reason(self, config = None):
        model = self.get_model(config)
        if model:
            return model.get_failure_reason()
        return None

    def as_object(self):
        obj = {key: getattr(self, key) for key in self.__dbfields  + self.__extfields if hasattr(self, key)} 
        obj["job_id"] = self.job_id
        obj["users"] = self.users
        obj["output_files"] = self.get_output_files()
        if self.meta:
            details = self.meta.copy()
            model = self.get_model()
            if model:
                details["model_desc"] = model.model_desc
                if "variant_filter" in details:
                    details["variant_filter_desc"] = model.get_filter_desc(details["variant_filter"])
            obj["details"] = details
            obj["hash"] = self.get_param_hash()
        return obj

    @staticmethod
    def get(job_id, config):
        if not re.match('[a-f0-9]{8}(-[a-f0-9]{4}){3}-[a-f0-9]{12}', job_id):
            return None
        job_folder = os.path.join(config.get("JOB_DATA_FOLDER", "./"), job_id)
        meta_path = os.path.expanduser(os.path.join(job_folder, "job.json"))
        if os.path.exists(meta_path):
            with open(meta_path) as meta_file:
                meta = json.load(meta_file)
        else:
           meta = dict()
        j = Job(job_id, meta, config=config)
        j.root_path = job_folder
        db = sql_pool.get_conn()
        cur = db.cursor(MySQLdb.cursors.DictCursor)
        sql = """
            SELECT
              bin_to_uuid(jobs.id) AS id,
              jobs.name AS name, jobs.description as description, jobs.user_id as user_id,
              jobs.status_id as status_id, statuses.name AS status,
              jobs.error_message AS error_message,
              DATE_FORMAT(jobs.creation_date, '%%Y-%%m-%%d %%H:%%i:%%s') AS creation_date,
              DATE_FORMAT(jobs.modified_date, '%%Y-%%m-%%d %%H:%%i:%%s') AS modified_date,
              jobs.is_active
            FROM jobs
            LEFT JOIN statuses ON jobs.status_id = statuses.id
            WHERE jobs.id = uuid_to_bin(%s)
            """
        cur.execute(sql, (job_id,))
        result = cur.fetchone()
        if result is not None:
            for x in Job.__dbfields + Job.__extfields:
                if x in result:
                    setattr(j, x, result[x])
            sql = """
                SELECT 
                    ju.user_id as user_id, ju.role_id as role_id,
                    role.role_name as role, users.email, 
                    DATE_FORMAT(ju.modified_date, '%%Y-%%m-%%d %%H:%%i:%%s') AS modified_date
                FROM job_users as ju
                    LEFT JOIN job_user_roles role on role.id = ju.role_id
                    LEFT JOIN users on users.id = ju.user_id
                WHERE 
                    ju.job_id = uuid_to_bin(%s)
                """
            cur.execute(sql, (job_id,))
            result = cur.fetchall()
            j.users = result
            return j
        else:
            return None

    @staticmethod
    def list_all_for_user(user_id, config=None, query=None):
        db = sql_pool.get_conn()
        params = query.params
        params["is_active"] = True
        params["user_id"] = user_id
        where, joins = Job.__params_to_where(params)
        results = Job.__list_by_sql_where_query(db, where=where, query=query)
        return results


    @staticmethod
    def list_all_for_user_shared_with(user_id, shared_user_id, config=None, query=None):
        db = sql_pool.get_conn()
        params = query.params
        params["is_active"] = True
        params["user_id"] = user_id
        params["shared_with"] = shared_user_id
        where, joins = Job.__params_to_where(params)
        results = Job.__list_by_sql_where_query(db, where=where, query=query)
        return results

    @staticmethod
    def list_all_for_phenotype(pheno_id, config=None, query=None):
        db = sql_pool.get_conn()
        params = query.params
        params["is_active"] = True
        params["pheno_id"] = pheno_id
        where, joins = Job.__params_to_where(params)
        results = Job.__list_by_sql_where_query(db, where=where, query=query)
        return results

    @staticmethod
    def list_all_for_genotype(geno_id, config=None, query=None):
        db = sql_pool.get_conn()
        params = query.params
        params["geno_id"] = geno_id
        where, joins = Job.__params_to_where(params)
        results = Job.__list_by_sql_where_query(db, where=where, query=query)
        return results

    @staticmethod
    def list_all_for_user_by_genotype(user_id, geno_id, config=None, query=None):
        db = sql_pool.get_conn()
        params = query.params
        params["is_active"] = True
        params["user_id"] = user_id
        params["geno_id"] = geno_id
        where, joins = Job.__params_to_where(params)
        results = Job.__list_by_sql_where_query(db, where=where, query=query)
        return results 

    @staticmethod
    def list_all_for_user_by_hash(user_id, param_hash, find_canceled=False, config=None):
        db = sql_pool.get_conn()
        where = ("jobs.param_hash = %s " +
            "AND jobs.id IN (SELECT job_id from job_users where user_id=%s) " +
            "AND jobs.is_active=1")
        if not find_canceled:
            where += " AND jobs.status_id not in (select id from statuses where name='canceled')"
        results = Job.__list_by_sql_where(db, where, (param_hash, user_id))
        return results 

    @staticmethod
    def list_all_active(config=None):
        db = sql_pool.get_conn()
        results = Job.__list_by_sql_where(db, ("("
            "statuses.name='queued' OR "
            "statuses.name='started' OR "
            "statuses.name='canceling')"))
        return results

    @staticmethod
    def list_all(config=None, query=None):
        db = sql_pool.get_conn()
        where, joins = Job.__params_to_where(query.params)
        result = Job.__list_by_sql_where_query(db, where=where, query=query)
        return result

    @staticmethod
    def __list_by_sql_where(db, where="", vals=()):
        where = WhereExpression(where, vals)
        result = Job.__list_by_sql_where_query(db, where=where, query=None)
        return result.results

    @staticmethod
    def __list_by_sql_where_query(db, where=None, query=None):
        cols = OrderedDict([("id", "bin_to_uuid(jobs.id)"),
            ("name", "jobs.name"),
            ("description", "jobs.description"),
            ("status", "statuses.name"),
            ("geno_id", "bin_to_uuid(jobs.geno_id)"),
            ("pheno_id", "bin_to_uuid(jobs.pheno_id)"),
            ("creation_date", "DATE_FORMAT(jobs.creation_date, '%%Y-%%m-%%d %%H:%%i:%%s')"),
            ("modified_date", "DATE_FORMAT(jobs.modified_date, '%%Y-%%m-%%d %%H:%%i:%%s')"),
            ("user_email", "users.email"),
            ("is_active", "jobs.is_active")])
        qcols = ["id", "name", "user_email", "status"]
        page, order_by, qsearch = SelectQuery.translate_query(query, cols, qcols)
        if not order_by:
            order_by = OrderClause(OrderExpression(cols["creation_date"], "DESC"))
        sqlcmd = (SelectQuery()
            .set_cols([ "{} AS {}".format(v,k) for k,v in cols.items()])
            .set_table("jobs")
            .add_join(TableJoin("statuses", "jobs.status_id = statuses.id"))
            .add_join(TableJoin("users", "jobs.user_id = users.id"))
            .set_where(where)
            .set_search(qsearch)
            .set_order_by(order_by)
            .set_page(page))
        return PagedResult.execute_select(db, sqlcmd)

    @staticmethod
    def __params_to_where(params):
        where = WhereAll()
        joins = dict()
        for k, v in params.items():
            if k == "user_id":
                where.add(WhereExpression("jobs.id IN (SELECT job_id from job_users where user_id=%s)", (v,)))
            elif k == "pheno_id":
                where.add(WhereExpression("jobs.pheno_id = uuid_to_bin(%s)", (v,)))
            elif k == "geno_id":
                where.add(WhereExpression("jobs.geno_id = uuid_to_bin(%s)", (v, )))
            elif k == "shared_with":
                where.add(WhereExpression("jobs.id IN (SELECT job_id from job_users where user_id=%s and role_id!=1)", (v,)))
            elif k == "is_active":
                if v:
                    where.add(WhereExpression("jobs.is_active=1"))
                else:
                    where.add(WhereExpression("jobs.is_active=0"))
            else:
                raise Exception("Parameter {} Not Recognized".format(k))
        return where, joins

    @staticmethod
    def create(job_id, values):
        db = sql_pool.get_conn()
        cur = db.cursor()
        cur.execute("""
            INSERT INTO jobs (id, name, description, user_id, geno_id, pheno_id, param_hash, status_id)
            VALUES (uuid_to_bin(%s), %s, %s, %s, uuid_to_bin(%s), uuid_to_bin(%s), %s,
            (SELECT id FROM statuses WHERE name = 'queued'))
            """, (job_id, values["name"], values["description"], values["user_id"], values["genotype"],
            values["phenotype"], values["param_hash"]))
        cur.execute("""
            INSERT INTO job_users(job_id, user_id, created_by, role_id)
            VALUES (uuid_to_bin(%s), %s, %s, (SELECT id FROM job_user_roles WHERE role_name = 'owner'))
            """, (job_id, values["user_id"], values["user_id"]))
        db.commit()


    @staticmethod
    def update_status(job_id, new_status, error_message=None, old_status=None):
        update_fields = [
            "status_id = (SELECT id FROM statuses WHERE name=%s LIMIT 1)",
            "modified_date = NOW()"
        ]
        update_values = [new_status]
        if error_message is not None:
            if error_message != "":
                update_fields.append("error_message = %s")
                update_values.append(error_message)
            else:
                update_fields.append("error_message = NULL")
        where_fields = ["id = uuid_to_bin(%s)"]
        where_values = [job_id]
        if old_status is not None:
            where_fields.append("status_id = (SELECT id FROM statuses WHERE name=%s LIMIT 1)")
            where_values.append(old_status)
        sql = "UPDATE jobs"
        sql += " SET " + ", ".join(update_fields)
        sql += " WHERE " + " AND ".join(where_fields)
        values = update_values + where_values
        db = sql_pool.get_conn()
        cur = db.cursor()
        cur.execute(sql, values)
        db.commit()

    @staticmethod
    def update(job_id, new_values):
        updateable_fields = ["name", "description"]
        fields = list(new_values.keys()) 
        values = [None if x=="" else x for x in new_values.values()]
        bad_fields = [x for x in fields if x not in updateable_fields]
        if len(bad_fields)>0:
            raise Exception("Invalid update field: {}".format(", ".join(bad_fields)))
        sql = "UPDATE jobs SET "+ \
            ", ".join(("{}=%s".format(k) for k in fields)) + \
            " WHERE id = uuid_to_bin(%s)"
        db = sql_pool.get_conn()
        cur = db.cursor()
        cur.execute(sql, values + [job_id])
        db.commit()

    @staticmethod
    def cancel(job_id):
        db = sql_pool.get_conn()
        cur = db.cursor()
        cur.execute("""
            UPDATE jobs SET status_id = (SELECT id FROM statuses WHERE name = 'canceling')
            WHERE id = uuid_to_bin(%s)
            """, (job_id, ))
        db.commit()

    @staticmethod
    def resubmit(job_id):
        db = sql_pool.get_conn()
        cur = db.cursor()
        cur.execute("""
            UPDATE jobs SET status_id = (SELECT id FROM statuses WHERE name = 'queued'), error_message=""
            WHERE id = uuid_to_bin(%s)
            """, (job_id, ))
        db.commit()

    @staticmethod
    def retire(job_id, config=None):
        job = Job.get(job_id, config)
        if job:
            db = sql_pool.get_conn()
            cur = db.cursor()
            sql = "UPDATE jobs SET is_active=0 WHERE id = uuid_to_bin(%s)"
            cur.execute(sql, (job_id, ))
            affected = cur.rowcount
            db.commit()
        else:
            raise Exception("Job {} Not Found".format(job_id))

    @staticmethod
    def purge(job_id, config=None):
        job = Job.get(job_id, config)
        if job:
            db = sql_pool.get_conn()
            cur = db.cursor()
            sql = "DELETE FROM job_users WHERE job_id = uuid_to_bin(%s)"
            cur.execute(sql, (job_id, ))
            users = cur.rowcount
            sql = "DELETE FROM jobs WHERE id = uuid_to_bin(%s)"
            cur.execute(sql, (job_id, ))
            affected = cur.rowcount
            db.commit()

            removed = False
            job_directory = job.root_path 
            if os.path.isdir(job_directory) and affected>0:
                try:
                    shutil.rmtree(job_directory)
                    removed = True
                except:
                    pass

            result = {"records": affected, "users": users, "files": removed}
            return result
        else:
            raise Exception("Job {} Not Found".format(job_id))

    @staticmethod
    def share_add_email(job_id, email, current_user, role=0, config=None):
        ex = None
        db = sql_pool.get_conn()
        user = User.from_email(email, db)
        if user is None:
            user = User.create({"email": email, "can_analyze": False}, db)["user"]
        try:
            cur = db.cursor()
            sql = """
                INSERT INTO job_users (job_id, user_id, role_id, created_by)
                VALUES (uuid_to_bin(%s), %s, %s, %s)
                """
            cur.execute(sql, (job_id, user.rid, role, current_user.rid))
            db.commit()
        except:
            ex = sys.exc_info()[1]
        finally:
            cur.close()
        if ex is not None:
            raise ex
        return True

    @staticmethod
    def share_drop_email(job_id, email, current_user, role=0, config=None):
        ex = None
        db = sql_pool.get_conn()
        user = User.from_email(email, db)
        if user is None:
            return False
        try:
            cur = db.cursor()
            sql = """
                DELETE FROM job_users
                WHERE job_id=uuid_to_bin(%s) AND user_id=%s
                """
            cur.execute(sql, (job_id, user.rid))
            db.commit()
        except:
            ex = sys.exc_info()[1]
        finally:
            cur.close()
        if ex is not None:
            raise ex
        return True

    @staticmethod
    def share_drop_collaborator(owner_id, collaborator_id, config=None):
        ex = None
        db = sql_pool.get_conn()
        owner = User.from_id(owner_id, db)
        collaborator = User.from_id(collaborator_id, db)
        if owner is None:
            raise Exception("Unrecognized owner id: {}".format(owner_id))
        if collaborator is None:
            raise Exception("Unrecognized collaborator id: {}".format(collaborator_id))
        records = 0
        try:
            cur = db.cursor()
            sql = """
                DELETE FROM job_users
                WHERE user_id=%s and job_id in
                    (SELECT * FROM (SELECT job_id from job_users where user_id=%s and role_id=1) as hack)
                """
            cur.execute(sql, (collaborator.rid, owner.rid))
            records = cur.rowcount
            db.commit()
        except:
            ex = sys.exc_info()[1]
        finally:
            cur.close()
        if ex is not None:
            raise ex
        result = {"records": records}
        return result

    @staticmethod
    def counts(by=None, filters=None, config=None):
        join_users = False
        join_geno = False
        join_status = False
        if not by:
            by = []
        elif isinstance(by, str):
            by = by.split(",")
        if not filters:
            filters = []
        elif isinstance(filters, str):
            filters = filters.split(",")
        select = []
        group_by = []
        columns = []
        wheres = []
        for field in by:
            if field=="month":
                select += [ "DATE_FORMAT(jobs.creation_date, '%Y-%m') as month"]
                group_by += [ "DATE_FORMAT(jobs.creation_date, '%Y-%m')"]
                columns += ["month"]
            elif field == "year":
                select += ["year(jobs.creation_date) as year"]
                group_by += ["year(jobs.creation_date)"]
                columns += ["year"]
            elif field == "user":
                select += ["COALESCE(users.full_name, users.email) as user"]
                group_by += ["users.id"]
                columns += ["user"]
                join_users = True
            elif field == "status":
                select += ["statuses.name as status"]
                group_by += ["statuses.name"]
                columns += ["status"]
                join_status = True
            elif field == "geno":
                select += ["genotypes.name as geno"]
                group_by += ["genotypes.name"]
                columns += ["geno"]
                join_geno = True
            else:
                raise Exception("Unrecognized field: {}".format(field))
        for filt in filters:
            if filt == "successful":
                wheres += ["jobs.status_id = (select id from statuses where name = 'succeeded')"]
            else:
                raise Exception("Unrecognized filter: {}".format(filt))
        select += ["COUNT(*) as count"]
        columns += ["count"]
        sql = "SELECT " + ", ".join(select)
        sql += " FROM jobs"
        if join_users:
            sql += " JOIN users on jobs.user_id = users.id"
        if join_status:
            sql += " JOIN statuses on jobs.status_id = statuses.id"
        if join_geno:
            sql += " JOIN genotypes on jobs.geno_id = genotypes.id"
        if len(wheres):
            sql += " WHERE (" + "), (".join(wheres) + ")"
        if len(group_by):
            sql += " GROUP BY " + ", ".join(group_by)


        db = sql_pool.get_conn()
        cur = db.cursor(MySQLdb.cursors.DictCursor)
        cur.execute(sql)
        results = cur.fetchall()
        return {"header": {"columns": columns}, "data": results}

    @staticmethod
    def calc_param_hash(meta):
        meta_clean = meta.copy();
        meta_clean.pop("name", None)
        meta_clean.pop("user_id", None)
        meta_clean.pop("response_desc", None)
        job_def_string = json.dumps(meta_clean, sort_keys=True)
        return hashlib.md5(job_def_string.encode('utf-8')).hexdigest() def check_view_job(f):
    return splat_args(inject_job(can_view_job(f)), f) def check_edit_job(f):
    return splat_args(inject_job(can_edit_job(f)), f) def can_user_edit_job(user, job):
    if not user or not job:
        return False
    user_id = user.rid
    if not user.is_active():
        return False
    if user_id == job.user_id:
        return True
    if user.is_admin():
        return True
    return False def check_edit_pheno(f):
    return splat_args(inject_pheno(can_edit_pheno(f)), f) def admin_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not current_user.is_admin():
            return "You do not have access", 403 
        return f(*args, **kwargs)
    return decorated_function def check_view_pheno(f):
    return splat_args(inject_pheno(can_view_pheno_page(f)), f) class Genotype:
    __dbfields = ["id", "name", "build", "is_active", "creation_date"]

    def __init__(self, geno_id, meta=None):
        self.geno_id = geno_id
        if meta is None:
            self.meta = {}
        else:
            self.meta = meta
        self.name = None
        self.build = None
        self.creation_date = None
        self.is_active = None
        self.root_path = ""
        self.build_info = {} 
       
    def get_vcf_path(self, chrom=1, must_exist=False):
        vcf_stub = ""
        chrom = str(chrom).replace("chr","")
        if "vcfs" in self.meta:
            vcfs = self.meta["vcfs"]
            if type(vcfs) is dict:
                if chrom in vcfs:
                    vcf_stub = vcfs[chrom]
                elif "*" in vcfs:
                    vcf_stub = vcfs["*"]
            else:
                vcf_stub = vcfs
        if vcf_stub == "":
            vcf_stub = "vcfs/chr{0}.vcf.gz"
        vcf_stub = vcf_stub.replace("*","{0}").format(chrom)
        vcf_path = self.relative_path(vcf_stub)
        if must_exist and not os.path.exists(vcf_path):
            return None
        return vcf_path

    def get_vcf_anno_path(self, chrom=1, must_exist=False):
        anno_stub = ""
        chrom = str(chrom).replace("chr","")
        if "annovcfs" in self.meta:
            anno = self.meta["annovcfs"]
            if type(anno) is dict:
                if chrom in anno:
                    anno_stub = anno[chrom]
                elif "*" in anno:
                    anno_stub = anno["*"]
            else:
                anno_stub = anno
        anno_stub = anno_stub.replace("*","{0}").format(chrom)
        if anno_stub == "":
            return None
        anno_path = self.relative_path(anno_stub)
        if must_exist and not os.path.exists(anno_path):
            return None
        return anno_path

    def get_sav_path(self, chrom=1, must_exist=False):
        sav_stub = ""
        chrom = str(chrom).replace("chr","")
        if "savs" in self.meta:
            savs = self.meta["savs"]
            if type(savs) is dict:
                if chrom in savs:
                    sav_stub = savs[chrom]
                elif "*" in savs:
                    sav_stub = savs["*"]
            else:
                sav_stub = savs
        if sav_stub == "":
            sav_stub = "savs/chr{0}.sav"
        sav_stub = sav_stub.replace("*","{0}").format(chrom)
        sav_path = self.relative_path(sav_stub)
        if must_exist and not os.path.exists(sav_path):
            return None
        return sav_path

    def get_groups_path(self, group, must_exist=False):
        grp_stub = ""
        if "groups" in self.meta:
            groups = self.meta["groups"]
            if type(groups) is dict:
                if group in groups:
                    grp_stub = groups[group]
                elif "*" in groups:
                    grp_stub = groups["*"]
            else:
                grp_stub = groups
        if grp_stub == "":
            grp_stub = "groups/{0}.grp"
        grp_stub = grp_stub.replace("*","{0}").format(group)
        grp_path = self.relative_path(grp_stub)
        if must_exist and not os.path.exists(grp_path):
            return None
        return grp_path

    def get_kinship_path(self, must_exist=False):
        kinship_stub = self.meta.get("kinship_path", "kinship/kinship.kin")
        kinship_path = self.relative_path(kinship_stub)
        if must_exist and not os.path.exists(kinship_path):
            return None
        return kinship_path

    def get_stats(self):
        if "stats" in self.meta:
           return self.meta["stats"] 
        elif "stats_path" in self.meta:
            stats_stub = self.meta.get("stats_path", "stats.json")
            stats_path = self.relative_path(stats_stub)
            with open(stats_path) as infile:
                stats = json.load(infile)
            self.meta["stats"] = stats
            return stats
        return dict() 

    def get_readme(self):
        if "readme" in self.meta:
           return self.meta["readme"]
        elif "readme_path" in self.meta:
            readme_stub = self.meta.get("readme_path")
            readme_path = self.relative_path(readme_stub)
            if os.path.isfile(readme_path):
                with open(readme_path) as infile:
                    readme = infile.read()
                self.meta["readme"] = readme
                return readme
        return ""

    def get_chromosomes(self):
        if "chrs" in self.meta:
            chrs = self.meta["chrs"]
            if chrs == "autosomes":
                chrs = " ".join(map(str, range(1,23)))
            elif chrs == "autosomesX":
                chrs = " ".join(map(str, range(1,23))) + " X"
            return chrs
        return None

    def get_chromosome_ranges(self):
        if "chr_ranges" in self.meta:
            chr_ranges = self.meta["chr_ranges"]
            if type(chr_ranges) == str:
                path = self.relative_path(chr_ranges)
                if not os.path.exists(path):
                    raise Exception("chr_ranges path not found: {}".format(path))
                chrom_list = []
                with open(path, "rt") as lines:
                    for line in lines:
                        if line.startswith("#"):
                            continue
                        chrom, start, stop = line.split()[0:3]
                        start = int(start)
                        stop = int(stop)
                        chrom_list.append({"chrom": chrom, "start": start, "stop": stop})
                return chrom_list
            else:
                raise Exception("Unexpected type for chr_ranges: {}".format(type(chr_ranges)))
        return None

    def get_info_stats(self):
        if "info_stats_path" in self.meta:
            stats_stub = self.meta.get("info_stats_path", "info.json")
            stats_path = self.relative_path(stats_stub)
            with open(stats_path) as infile:
                stats = json.load(infile)
            return stats
        else:
            return dict()

    def get_samples(self):
        path = self.get_samples_path()
        if path:
            with open(path) as infile:
                for sample in infile:
                    yield sample.rstrip("\n")

    def get_samples_path(self):
        samples_path = self.meta.get("samples_path", "")
        samples_path = self.relative_path(samples_path)
        if os.path.isfile(samples_path):
            return samples_path
        else:
            return None

    def get_pca_genotypes_path(self, must_exist=False):
        geno_path = self.meta.get("pca_genotypes_path", None)
        if not geno_path:
            return None
        geno_path = self.relative_path(geno_path)
        if must_exist and not os.path.exists(geno_path):
            return None
        return geno_path

    def get_build_info(self, config, build):
        if build in config.get("BUILD_REF", {}):
            build_info = config.get("BUILD_REF").get(build)
        else:
            raise Exception("Build information not found: {}".format(build))
        return build_info

    def get_build_ref_path(self):
        return self.build_info.get("fasta", None)

    def get_build_nearest_gene_path(self):
        return self.build_info.get("nearest_gene_bed", None)

    def get_phenotypes(self):
        if not "phenotypes" in self.meta:
            return None
        phenos = self.meta["phenotypes"]
        if not isinstance(phenos, list):
            phenos = [phenos]
        result = []
        for p in phenos:
            pmeta = None
            if "meta" in p:
                with open(self.relative_path(p["meta"])) as f:
                    pmeta = json.load(f)
            result.append({"name": p.get("name","pheno"), "meta": pmeta})
        return result

    def get_pheno_reader(self, index=0):
        if not "phenotypes" in self.meta:
            return None
        phenos = self.meta["phenotypes"]
        if not isinstance(phenos, list):
            phenos = [phenos]
        p = phenos[index]
        pmeta = None
        if "meta" in p:
            with open(self.relative_path(p["meta"])) as f:
                pmeta = json.load(f)
        return PhenoReader(self.relative_path(p["file"]), pmeta)

    def get_geno_reader(self, config):
        return GenoReader(self, config)

    def get_ld_info(self, config):
        if not "ld_server" in self.meta:
            return None
        metald = self.meta["ld_server"]
        ld_panel = self.geno_id
        ld_population = "ALL"
        ld_build = self.build
        if type(metald) == bool:
            if not metald:
                return None
        elif type(metald) == str:
            ld_panel = metald
        elif isinstance(metald, dict):
            ld_panel = metald.get("panel", ld_panel)
            ld_population = metald.get("population", ld_population)
            ld_build = metald.get("build", ld_build)
        else:
            raise Exception("Unrecognized type for ld_server config:" + type(metald))
        return {"panel": ld_panel, "population": ld_population,
            "build": ld_build}

    def relative_path(self, *args):
        return os.path.expanduser(os.path.join(self.root_path, *args))

    def as_object(self, include_meta=False):
        obj = {"geno_id": self.geno_id, 
            "name": self.name, 
            "build": self.build, 
            "creation_date": self.creation_date,
            "is_active": self.is_active}
        obj["stats"] = self.get_stats()
        obj["phenos"] = self.get_phenotypes()
        obj["readme"] = self.get_readme()
        avail = dict()
        avail["vcf"] = True if self.get_vcf_path(must_exist=True) else False
        avail["sav"] = True if self.get_sav_path(must_exist=True) else False
        avail["kinship"] = True if self.get_kinship_path(must_exist=True) else False
        avail["snps"] = True if self.get_pca_genotypes_path(must_exist=True) else False
        avail["group_nonsyn"] = True if self.get_groups_path("nonsyn", must_exist=True) else False
        obj["avail"] = avail
        if include_meta:
            obj["meta"] = self.meta
        return obj

    @staticmethod
    def get(geno_id, config):
        geno_folder = os.path.join(config.get("GENO_DATA_FOLDER", "./"), geno_id)
        meta_path = os.path.expanduser(os.path.join(geno_folder, "meta.json"))
        if os.path.exists(meta_path):
            with open(meta_path) as meta_file:
                meta = json.load(meta_file)
        else:
           meta = dict()
        db = sql_pool.get_conn()
        results = Genotype.__list_by_sql_where(db, "id=uuid_to_bin(%s)", (geno_id,))
        result = results[0] if results else None
        if result is not None:
            g = Genotype(geno_id, meta)
            g.name = result["name"]
            g.build = result["build"]
            g.creation_date = result["creation_date"]
            g.is_active = result["is_active"]
            g.root_path = geno_folder
            g.build_info = g.get_build_info(config, g.build)
        else:
            g = None
        return g
        
    @staticmethod
    def list_all_for_user(user_id=None, config=None, query=None):
        db = sql_pool.get_conn()
        where = WhereExpression("is_active=1")
        results = Genotype.__list_by_sql_where_query(db, where=where, query=query)
        return results

    @staticmethod
    def list_all(config=None, query=None):
        db = sql_pool.get_conn()
        results = Genotype.__list_by_sql_where_query(db, query=query)
        return results

    @staticmethod
    def __list_by_sql_where(db, where="", vals=()):
        where = WhereExpression(where, vals)
        result = Genotype.__list_by_sql_where_query(db, where=where, query=None)
        return result.results

    @staticmethod
    def __list_by_sql_where_query(db, where=None, query=None):
        cols = OrderedDict([("id", "bin_to_uuid(genotypes.id)"),
            ("name", "genotypes.name"),
            ("build", "genotypes.build"),
            ("creation_date", "DATE_FORMAT(genotypes.creation_date, '%%Y-%%m-%%d %%H:%%i:%%s')"),
            ("is_active", "genotypes.is_active")])
        qcols = ["name", "build", "creation_date"]
        page, order_by, qsearch = SelectQuery.translate_query(query, cols, qcols)
        if not order_by:
            order_by = OrderClause(OrderExpression(cols["creation_date"], "DESC"))
        sqlcmd = (SelectQuery()
            .set_cols([ "{} AS {}".format(v,k) for k,v in cols.items()])
            .set_table("genotypes")
            .set_where(where)
            .set_search(qsearch)
            .set_order_by(order_by)
            .set_page(page))
        return PagedResult.execute_select(db, sqlcmd)

    @staticmethod
    def create(new_values, db=None, config=None):
        updateable_fields = [x for x in Genotype.__dbfields]
        required_fields = ["id", "name", "build"]
        fields = list(new_values.keys()) 
        values = list(new_values.values())
        bad_fields = [x for x in fields if x not in updateable_fields]
        if len(bad_fields)>0:
            raise Exception("Invalid field: {}".format(", ".join(bad_fields)))
        missing_fields = [x for x in required_fields if x not in fields]
        if len(missing_fields)>0:
            raise Exception("Missing required fields: {}".format(", ".join(missing_fields)))
        empty_fields = [x for x in required_fields if len(new_values[x])<1]
        if len(empty_fields)>0:
            raise Exception("Missing values for fields: {}".format(", ".join(empty_fields)))
        builds = config.get("BUILD_REF", {}).keys()
        if new_values["build"] not in config.get("BUILD_REF", {}).keys():
            raise Exception("Unrecognized build: {} (known: {})".format(
                new_values["build"], ", ".join(builds)))
        if db is None:
            db = sql_pool.get_conn()
        sql = "INSERT INTO genotypes (" + \
            ", ".join(fields)+ \
            ") values (" + \
            ", ".join(["uuid_to_bin(%s)" if x=="id" else "%s" for x in fields]) + \
            ")"
        cur = db.cursor(MySQLdb.cursors.DictCursor)
        cur.execute(sql, values)
        db.commit()
        new_geno = Genotype.get(new_values["id"], config=config)
        result = {"geno": new_geno}
        return result class Phenotype:
    __dbfields = ["name", "description", "orig_file_name", "md5sum",
        "user_id", "creation_date", "is_active"]

    def __init__(self, pheno_id, meta=None):
        self.pheno_id = pheno_id
        self.name = None
        self.description = None
        self.orig_file_name = None
        self.md5sum = None
        self.user_id = None
        self.creation_date = None
        self.root_path = "" 
        self.meta = meta

    def set_meta(self, meta, config):
        self.meta = meta
        pheno_folder = os.path.join(config.get("PHENO_DATA_FOLDER", "./"), self.pheno_id)
        meta_path = os.path.expanduser(os.path.join(pheno_folder, "meta.json"))
        with open(meta_path, "w") as f:
            json.dump(meta, f, indent=2)
       
    def get_raw_path(self):
        return self.relative_path("pheno.txt")

    def get_pheno_reader(self):
        return PhenoReader(self.get_raw_path(), self.meta)

    def relative_path(self, *args):
        return os.path.expanduser(os.path.join(self.root_path, *args))

    def get_column_class(self, covar_name):
        covar = self.__get_column(covar_name)
        return covar.get("class", "")

    def get_column_type(self, covar_name):
        covar = self.__get_column(covar_name)
        return covar.get("type", "")

    def get_column_levels(self, covar_name):
        covar = self.__get_column(covar_name)
        return covar.get("levels", [])

    def check_has_id_col(self):
        sample_id_col = [x for x in self.meta.get("columns", []) if x.get("class", "")=="sample_id"]
        if len(sample_id_col) != 1:
            return False
        return True

    def check_usable(self):
        errors = []
        if not self.check_has_id_col():
            errors.append({
                "type": "no_sample_id_col",
                "desc": "Unable to find sample ID column"})
        return len(errors)==0, errors

    def get_kinship_path(self, geno_id, must_exist=True):
        kinship = self.meta.get("kinship", None)
        if kinship:
            kinship_path = kinship.get(geno_id, None)
            if kinship_path:
                kinship_path = self.relative_path(kinship_path)
                if not must_exist or os.path.exists(kinship_path):
                    return kinship_path
        return None

    def set_sample_id_col(self, column, known_sample_ids = None, config=None):
        pr = self.get_pheno_reader()
        meta = pr.infer_meta(sample_ids = known_sample_ids, sample_id_column = column)
        if "id_error" in meta:
            raise Exception(meta["id_error"])
        else:
            self.set_meta(meta, config=config)


    def as_object(self):
        obj = {key: getattr(self, key) for key in self.__dbfields if hasattr(self, key)} 
        obj["pheno_id"] = self.pheno_id
        obj["meta"] = self.meta
        obj["has_sample_id_col"] = self.check_has_id_col()
        obj["is_usable"], obj["errors"] = self.check_usable()
        return obj

    def __get_column(self, covar_name):
        covar = [x for x in self.meta.get("columns", []) if x.get("name", "")==covar_name]
        if len(covar) != 1:
            raise Exception("Could not find column: {}".format(covar_name))
        return covar[0]

    @staticmethod
    def get(pheno_id, config):
        where = WhereExpression("phenotypes.id = uuid_to_bin(%s)", (pheno_id,))
        return Phenotype.__get_by_sql_where(where=where, config=config)

    @staticmethod
    def get_by_hash_user(filehash, user_id, config):
        where = WhereAll(
            WhereExpression("md5sum=%s", (filehash,)),
            WhereExpression("user_id=%s", (user_id,)),
            WhereExpression("phenotypes.is_active=1")
        )
        return Phenotype.__get_by_sql_where(where=where, config=config)
    
    @staticmethod
    def __get_by_sql_where(where, config):
        db = sql_pool.get_conn()
        results = Phenotype.__list_by_sql_where_query(db, where=where).results
        if len(results) != 1:
            return None
        result = results[0]
        pheno_id = result["id"]
        pheno_folder = os.path.join(config.get("PHENO_DATA_FOLDER", "./"), pheno_id)
        meta_path = os.path.expanduser(os.path.join(pheno_folder, "meta.json"))
        if os.path.exists(meta_path):
            with open(meta_path) as meta_file:
                meta = json.load(meta_file)
        else:
           meta = dict()
        p = Phenotype(pheno_id, meta)
        p.root_path = pheno_folder
        for x in Phenotype.__dbfields:
            if x in result:
                setattr(p, x, result[x])
        return p

    @staticmethod
    def list_all_for_user(user_id, config=None, query=None):
        db = sql_pool.get_conn()
        where = WhereAll(
            WhereExpression("phenotypes.is_active = 1"),
            WhereExpression("phenotypes.user_id = %s", (user_id,))
        )
        result = Phenotype.__list_by_sql_where_query(db, where=where, query=query)
        return result

    @staticmethod
    def list_all(config=None, query=None):
        db = sql_pool.get_conn()
        result = Phenotype.__list_by_sql_where_query(db, query=query)
        return result

    @staticmethod
    def __list_by_sql_where_query(db, where=None, query=None):
        cols = OrderedDict([("id", "bin_to_uuid(phenotypes.id)"),
            ("name", "phenotypes.name"),
            ("description", "phenotypes.description"),
            ("user_email", "users.email"),
            ("user_id", "phenotypes.user_id"),
            ("orig_file_name", "phenotypes.orig_file_name"),
            ("md5sum", "phenotypes.md5sum"),
            ("creation_date", "DATE_FORMAT(phenotypes.creation_date, '%%Y-%%m-%%d %%H:%%i:%%s')"),
            ("is_active", "phenotypes.is_active")])
        qcols = ["id", "name", "user_email"]
        page, order_by, qsearch = SelectQuery.translate_query(query, cols, qcols)
        if not order_by:
            order_by = OrderClause(OrderExpression(cols["creation_date"], "DESC"))
        sqlcmd = (SelectQuery()
            .set_cols([ "{} AS {}".format(v,k) for k,v in cols.items()])
            .set_table("phenotypes")
            .add_join(TableJoin("users", "phenotypes.user_id = users.id"))
            .set_where(where)
            .set_search(qsearch)
            .set_order_by(order_by)
            .set_page(page))
        return PagedResult.execute_select(db, sqlcmd)

    @staticmethod
    def add(new_values):
        if "id" in new_values:
            pheno_id = new_values["id"]
            del new_values["id"]
        else:
            raise Exception("Missing required field: id")
        updateable_fields = ["name", "user_id", "orig_file_name", "md5sum"]
        fields = list(new_values.keys())
        values = [None if x=="" else x for x in new_values.values()]
        bad_fields = [x for x in fields if x not in updateable_fields]
        if len(bad_fields)>0:
            raise Exception("Invalid field: {}".format(", ".join(bad_fields)))
        sql = "INSERT INTO phenotypes (id, {}) VALUES (uuid_to_bin(%s), {})".format( \
            ", ".join(fields),  \
            ", ".join(["%s"] * len(fields)) )
        db = sql_pool.get_conn()
        cur = db.cursor()
        cur.execute(sql, [pheno_id] + values)
        db.commit()

    @staticmethod
    def update(pheno_id, new_values):
        updateable_fields = ["name", "description"]
        fields = list(new_values.keys()) 
        values = [None if x=="" else x for x in new_values.values()]
        bad_fields = [x for x in fields if x not in updateable_fields]
        if len(bad_fields)>0:
            raise Exception("Invalid update field: {}".format(", ".join(bad_fields)))
        sql = "UPDATE phenotypes SET "+ \
            ", ".join(("{}=%s".format(k) for k in fields)) + \
            " WHERE id = uuid_to_bin(%s)"
        db = sql_pool.get_conn()
        cur = db.cursor()
        cur.execute(sql, values + [pheno_id])
        db.commit()

    @staticmethod
    def retire(pheno_id, config=None):
        pheno = Phenotype.get(pheno_id, config)
        if pheno:
            db = sql_pool.get_conn()
            cur = db.cursor()
            sql = "UPDATE phenotypes SET is_active=0 WHERE id = uuid_to_bin(%s)"
            cur.execute(sql, (pheno_id, ))
            affected = cur.rowcount
            db.commit()
        else:
            raise Exception("Pheno {} Not Found".format(pheno_id))

    @staticmethod
    def purge(pheno_id, config=None):
        pheno = Phenotype.get(pheno_id, config)
        if pheno:
            db = sql_pool.get_conn()
            cur = db.cursor()
            sql = "DELETE FROM phenotypes WHERE id = uuid_to_bin(%s)"
            cur.execute(sql, (pheno_id, ))
            affected = cur.rowcount
            db.commit()

            removed = False
            pheno_directory = pheno.root_path 
            if os.path.isdir(pheno_directory) and affected>0:
                try:
                    shutil.rmtree(pheno_directory)
                    removed = True
                except:
                    pass

            result = {"records": affected, "files": removed}
            return result
        else:
            raise Exception("Pheno {} Not Found".format(pheno_id)) class Notice:
    __dbfields = ["id", "message", "start_date", "end_date"]

    def __init__(self, notice_id, message=None, start_date=None, end_date=None):
        self.notice_id = notice_id
        self.message = message
        self.start_date = start_date
        self.end_date = end_date

    def as_object(self):
        obj = {"notice_id": self.notice_id,
            "message": self.message,
            "start_date": self.start_date,
            "end_date": self.end_date}
        return obj

    @staticmethod
    def get(notice_id, db=None):
        if db is None:
            db = sql_pool.get_conn()
        where = WhereExpression("id=%s", (notice_id, ))
        return Notice.__get_by_sql_where(db, where)

        
    @staticmethod
    def list_current(config = None, query=None):
        db = sql_pool.get_conn()
        params = (query and query.params) or dict()
        params["is_active"] = True
        where, joins = Notice.__params_to_where(params)
        result = Notice.__list_by_sql_where_query(db, where=where, query=query)
        return result

    @staticmethod
    def list_all(config=None, query=None):
        db = sql_pool.get_conn()
        where, joins = Notice.__params_to_where(query.params)
        result = Notice.__list_by_sql_where_query(db, where=where, query=query)
        return result

    @staticmethod
    def __get_by_sql_where(db, where=None):
        if db is None:
            db = sql_pool.get_conn()
        results = Notice.__list_by_sql_where_query(db, where, None).results
        if len(results)!=1:
            return None
        res = results[0]
        return Notice(res["id"], res["message"],
            res["start_date"], res["end_date"])

    @staticmethod
    def __list_by_sql_where_query(db, where=None, query=None):
        cols = OrderedDict([("id", "id"),
            ("message", "message"),
            ("start_date", "DATE_FORMAT(start_date, '%%Y-%%m-%%d %%H:%%i:%%s')"),
            ("end_date", "DATE_FORMAT(end_date, '%%Y-%%m-%%d %%H:%%i:%%s')")
        ])
        qcols = ["id", "message", "start_date", "end_date"]
        page, order_by, qsearch = SelectQuery.translate_query(query, cols, qcols)
        if not order_by:
            order_by = OrderClause(OrderExpression(cols["start_date"], "DESC"))
        sqlcmd = (SelectQuery()
            .set_cols([ "{} AS {}".format(v,k) for k,v in cols.items()])
            .set_table("notices")
            .set_where(where)
            .set_search(qsearch)
            .set_order_by(order_by)
            .set_page(page))
        return PagedResult.execute_select(db, sqlcmd)

    @staticmethod
    def __params_to_where(params):
        where = WhereAll()
        joins = dict()
        for k, v in params.items():
            if k == "is_active":
                where.add(WhereExpression("start_date <= CURRENT_TIMESTAMP() and (end_date is NULL or end_date > CURRENT_TIMESTAMP())"))
            else:
                raise Exception("Parameter {} Not Recognized".format(k))
        return where, joins

    @staticmethod
    def create(new_values, db=None):
        updateable_fields = [x for x in Notice.__dbfields if x != "id"]
        fields = list(new_values.keys())
        values = [None if x=="" else x for x in new_values.values()]
        bad_fields = [x for x in fields if x not in updateable_fields]
        if db is None:
            db = sql_pool.get_conn()
        if len(bad_fields)>0:
            raise Exception("Invalid create field: {}".format(", ".join(bad_fields)))
        sql = "INSERT INTO notices (" + \
            ", ".join(fields)+ \
            ") values (" + \
            ", ".join(["%s"] * len(values)) + \
            ")"
        cur = db.cursor(MySQLdb.cursors.DictCursor)
        cur.execute(sql, values)
        db.commit()
        new_id = cur.lastrowid
        new_notice = Notice.get(new_id, None)
        result = {"notice": new_notice}
        return result

    @staticmethod
    def update(notice_id, new_values):
        updateable_fields = [x for x in Notice.__dbfields if x != "id"]
        fields = list(new_values.keys())
        values = [None if x=="" else x for x in new_values.values()]
        bad_fields = [x for x in fields if x not in updateable_fields]
        if len(bad_fields)>0:
            raise Exception("Invalid update field: {}".format(", ".join(bad_fields)))
        sql = "UPDATE notices SET "+ \
            ", ".join(("{}=%s".format(k) for k in fields)) + \
            " WHERE id = %s"
        db = sql_pool.get_conn()
        cur = db.cursor()
        cur.execute(sql, values + [int(notice_id)])
        db.commit() class PhenoReader:
    def __init__(self, path, meta=None):
        self.path = path
        if meta:
            self.meta = meta
        else:
            self.meta = self.infer_meta()

    def infer_meta(self, sample_ids=None, sample_id_column=None):
        return infer_meta(self.path, known_sample_ids=sample_ids, sample_id_column=sample_id_column)

    def get_dialect(self, opts=None):
        class dialect(csv.Dialect):
            pass
        if opts is None and self.meta and "layout" in self.meta:
            opts = {k[4:]:v for (k,v) in self.meta["layout"].items() if k.startswith("csv_")}
            for (k, v) in opts.items():
                setattr(dialect, k, v)
            return dialect
        else:
            return None

    def get_column_missing_values(self):
        cols = self.get_columns();
        return { v["name"]: v.get("missing",None) for v in cols }

    def get_column_indexes(self):
        cols = self.get_columns();
        return { v["name"]: i for (i,v) in enumerate(cols)}

    def get_columns(self):
        if self.meta and self.meta["columns"]:
            return self.meta["columns"]
        else:
            return []

    def get_sample_column_index(self):
        sample_col = next(iter([x for x in self.get_columns() if x["class"]=="sample_id"]), None)
        if not sample_col:
            return None
        return self.get_column_indexes()[sample_col["name"]]

    def row_extractor(self, samples=None):
        dialect = self.get_dialect()
        if "layout" in self.meta and "skip" in self.meta["layout"]:
            skip = self.meta['layout']['skip']
        else:
            skip = 0
        sample_col_idx = self.get_sample_column_index()
        with open(self.path, 'r', encoding=self.meta.get("encoding", "utf-8")) as csvfile:
            if not dialect:
                dialect = sniff_file(csvfile)
                csvfile.seek(0)
            if skip>0:
                [csvfile.readline() for i in range(skip)]
            cvr = csv.reader((row for row in csvfile if not row.startswith("#")), dialect)
            for row in cvr:
                if samples and sample_col_idx is not None and row[sample_col_idx] not in samples:
                    continue
                yield row

    def get_samples(self):
        sample_col_idx = self.get_sample_column_index()
        if sample_col_idx is None:
            return
        for row in self.row_extractor():
            yield row[sample_col_idx]

    @staticmethod
    def get_file_type(file):
        p = subprocess.Popen(["file", "-b", file], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
        human, err = p.communicate()
        p = subprocess.Popen(["file","-b","-i", file], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
        mime, err = p.communicate()
        return human.decode().rstrip(), mime.decode().rstrip()

    @staticmethod
    def is_text_file(file):
        human, mime = PhenoReader.get_file_type(file)
        istext = re.search(r"\btext\b", human) is not None
        return istext, human, mime   class SlurmJob:

    def __init__(self, job_id, job_directory, config = None):
        self.job_id = job_id
        self.job_directory = job_directory
        if config:
            self.config = config 
        else:
            self.config = dict()

    def get_batch_headers(self, model_plan):
        mem_per_cpu = model_plan.get("mem_per_cpu", self.config.get("JOB_MEM_PER_CPU", 6500))
        cores_per_job = model_plan.get("cores_per_job", self.config.get("JOB_CORES_PER_TASK", 56))
        
        sbatch_headers = ["#!/bin/bash"]

        if "SLURM_ACCOUNT" in self.config:
            sbatch_headers.append(
                "#SBATCH --account={}".format(self.config.get("SLURM_ACCOUNT")))

        sbatch_headers.extend((
            "#SBATCH --partition={}".format(self.config.get("QUEUE_PARTITION", "encore")),
            "#SBATCH --job-name=gasp_{}".format(self.job_id),
            "#SBATCH --mem-per-cpu={}".format(mem_per_cpu),
            "#SBATCH --chdir={}".format(self.job_directory),
            "#SBATCH --cpus-per-task={}".format(cores_per_job),
            "#SBATCH --time={}".format(self.config.get("JOB_TIME", "14-0")),
            "#SBATCH --nodes=1",
            "export OPENBLAS_NUM_THREADS=1"))

        return sbatch_headers

    def write_batch_script(self, batch_script_path, model_plan):
        with open(batch_script_path, "w") as f:
            f.write("\n".join(self.get_batch_headers(model_plan)))
            f.write("\n\n")
            f.write("\n".join(model_plan["commands"]))
            f.write("\n")

    def submit_job(self, model_spec):
        model = ModelFactory.get_for_model_spec(model_spec, self.job_directory, self.config)
        model_plan = model.prepare_job(model_spec)

        sbatch = self.config.get("QUEUE_JOB_BINARY", "sbatch")
        batch_script_path = self.relative_path("batch_script.sh")
        batch_output_path = self.relative_path("batch_script_output.txt")

        self.write_batch_script(batch_script_path, model_plan)
        with open(batch_output_path, "w") as f:
            try:
                subprocess.check_call([sbatch, batch_script_path], stdout=f)
            except subprocess.CalledProcessError as e:
                # log to server log
                print("SBATCH ERROR")
                print(e)
                raise Exception("Could not queue job") 
            except OSError:
                raise Exception("Could not find sbatch")
        return True

    def get_model(self, model_spec = None):
        if not model_spec:
            model_spec = self.load_model_spec()
        model = ModelFactory.get_for_model_spec(model_spec, self.job_directory, self.config)
        return model

    def resubmit_job(self):
        sbatch = self.config.get("QUEUE_JOB_BINARY", "sbatch")
        batch_script_path = self.relative_path("batch_script.sh")
        if not os.path.isfile(batch_script_path):
            raise Exception("Existing script file not found") 
        batch_output_path = self.relative_path("batch_script_output.txt")
        with open(batch_output_path, "w") as f:
            try:
                subprocess.check_call([sbatch, batch_script_path], stdout=f)
            except subprocess.CalledProcessError as e:
                # log to server log
                print("SBATCH ERROR")
                print(e)
                raise Exception("Could not queue job") 
            except OSError:
                raise Exception("Could not find sbatch")
        return True

    def cancel_job(self):
        scancel = self.config.get("CANCEL_JOB_BINARY", "scancel")
        batch_output_path = self.relative_path("batch_script_output.txt")
        try:
            with open(batch_output_path, 'r') as f:
                slurm_job_id = f.readline()
            slurm_job_id = [s for s in slurm_job_id.split() if s.isdigit()][0]
        except:
            raise Exception("Could not find job queue id ({})".format(self.job_id))
        try:
            subprocess.check_call([scancel, slurm_job_id])
        except subprocess.CalledProcessError:
            raise Exception("Could not scancel job") 
        except OSError:
            raise Exception("Could not find scancel")
        return True

    def get_progress(self):
        model = self.get_model()
        return model.get_progress()

    def load_model_spec(self):
        job_spec_path = self.relative_path("job.json")
        if not os.path.isfile(job_spec_path):
            raise Exception("Job spec file not found for job")
        with open(job_spec_path) as f:
            model_spec = json.load(f)
        return model_spec
        
    def relative_path(self, *args):
        return os.path.expanduser(os.path.join(self.job_directory, *args)) def get_queue():
    cols = [["job_id", "%i"], ["job_name", "%j"], ["state", "%t"],
        ["time", "%M"], ["reason", "%R"]]
    col_names = [x[0] for x in cols]
    col_formats = [x[1] for x in cols]
    cmd = ["/usr/cluster/bin/squeue", 
        "-u", pwd.getpwuid(os.getuid())[0], 
        "-p", "encore",
        "-o", "|".join(col_formats),
        "--noheader"]
    p = subprocess.Popen(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    squeue_out, squeue_err = p.communicate()
    queue = {"running": [], "queued": []}
    for line in squeue_out.decode().split("\n"):
        values = line.split("|")
        if len(values) != len(col_names):
            continue
        row = dict(zip(col_names, values))
        row["job_name"] = row["job_name"][5:]
        if row["state"]=="R":
            queue["running"].append(row)
        elif row["state"] == "PD":
            queue["queued"].append(row)
    return queue class ModelFactory:
    __models = []

    @staticmethod
    def list(config = {}):
        def desc(x):
            return {"code": x.model_code, "name": x.model_name, "description": x.model_desc,
                "depends": getattr(x, "depends",  []),
                "filters": getattr(x, "filters",  []),
                "response_class": getattr(x, "response_class", "*")}
        show_models = {x: 1 for x in config.get("AVAIL_MODELS", [])}
        def can_show(x):
            return len(show_models)==0 or x.model_code in show_models
        return [ desc(m) for m in ModelFactory.__models if can_show(m)]

    @staticmethod
    def register(m):
        if not hasattr(m, "model_code"):
            raise ValueError("Missing required attribite 'model_code'")
        ModelFactory.__models.append(m)

    @staticmethod
    def get(model_code, working_directory, config):
        for m in ModelFactory.__models:
            if m.model_code == model_code:
                return m(working_directory, config)
        raise ValueError("Unrecognized model type: {}".format(model_code))

    @staticmethod
    def get_for_model_spec(model_spec, working_directory, config):
        model_code = model_spec.get("type", None)
        if model_code is None:
            raise ValueError("Type not found for model")
        return ModelFactory.get(model_code, working_directory, config) def get_notifier():
    if "notifier" not in g:
        g.notifier = Notifier(current_app.config)
    return g.notifier class AccessTracker():
    @staticmethod
    def LogJobAccess(job_id, user_id = None, access_date = None):
        if user_id is None:
            user_id = current_user.rid
        if access_date is None:
            access_date = datetime.now().strftime('%Y-%m-%d')
        db = sql_pool.get_conn()
        cur = db.cursor(MySQLdb.cursors.DictCursor)
        sql = "INSERT INTO access_job_log " \
            "SET user_id = %s, " \
            "job_id = uuid_to_bin(%s),"  \
            "access_date = %s " \
            "ON DUPLICATE KEY UPDATE count=count+1;"
        params = (user_id, job_id, access_date)
        try:
            cur.execute(sql, params)
            db.commit()
        except (MySQLdb.Error, MySQLdb.Warning) as e:
            print("Logging error:  {}".format(e))

    def LogAPIAccess(user_id = None, access_date = None):
        if user_id is None:
            user_id = current_user.rid
        if access_date is None:
            access_date = datetime.now().strftime('%Y-%m-%d')
        db = sql_pool.get_conn()
        cur = db.cursor(MySQLdb.cursors.DictCursor)
        sql = "INSERT INTO access_api_log " \
            "SET user_id = %s, " \
            "access_date = %s " \
            "ON DUPLICATE KEY UPDATE count=count+1;"
        params = (user_id, access_date)
        try:
            cur.execute(sql, params)
            db.commit()
        except (MySQLdb.Error, MySQLdb.Warning) as e:
            print("Logging error:  {}".format(e))

    @staticmethod
    def counts(what=None, by=None, filters=None, config=None):
        if not by:
            by = []
        elif isinstance(by, str):
            by = by.split(",")
        if not filters:
            filters = []
        elif isinstance(filters, str):
            filters = filters.split(",")
        select = []
        group_by = []
        columns = []
        wheres = []
        table_from = "FROM access_job_log as access"
        join_users = True
        for field in by:
            if field=="day":
                select += [ "DATE_FORMAT(access.access_date, '%Y-%m-%d') as day"]
                group_by += [ "DATE_FORMAT(access.access_date, '%Y-%m-%d')"]
                columns += ["day"]
            elif field=="month":
                select += [ "DATE_FORMAT(access.access_date, '%Y-%m') as month"]
                group_by += [ "DATE_FORMAT(access.access_date, '%Y-%m')"]
                columns += ["month"]
            elif field == "year":
                select += ["year(access.access_date) as year"]
                group_by += ["year(access.access_date)"]
                columns += ["year"]
            elif field == "user":
                select += ["COALESCE(users.full_name, users.email) as user"]
                group_by += ["users.id"]
                columns += ["user"]
                join_users = True
            else:
                raise Exception("Unrecognized field: {}".format(field))
        for filt in filters:
            raise Exception("Unrecognized filter: {}".format(filt))
        if what=="jobs":
            select += ["COUNT(DISTINCT job_id) as count"]
        elif what=="users":
            select += ["COUNT(DISTINCT user_id) as count"]
        elif what=="api":
            select += ["CAST(SUM(count) as SIGNED) as count"]
            table_from = "FROM access_api_log as access"
        else:
            raise Exception("Unrecognized what: {}".format(what))
        columns += ["count"]
        sql = "SELECT " + ", ".join(select)
        sql += " " + table_from
        if join_users:
            sql += " JOIN users on access.user_id = users.id"
        if len(wheres):
            sql += " WHERE (" + "), (".join(wheres) + ")"
        if len(group_by):
            sql += " GROUP BY " + ", ".join(group_by)

        db = sql_pool.get_conn()
        cur = db.cursor(MySQLdb.cursors.DictCursor)
        cur.execute(sql)
        results = cur.fetchall()
        return {"header": {"columns": columns}, "data": results} class OrderClause:
class OrderExpression:
class TableJoin:
class WhereExpression:
class WhereGroup:
class WhereAll(WhereGroup):
class WhereAny(WhereGroup):
class WhereClause(WhereAll):
class SelectQuery:
class PagedResult:
class DBException(Exception):
    def __init__(self, *exprs):
    def add(self, column_order):
    def to_clause(self):
    def __init__(self, column, direction="ASC"):
    def to_clause(self):
    def __repr__(self):
    def __init__(self, table, on, join_type="LEFT"):
    def to_clause(self):
    def __init__(self, where="", vals=()):
    def count(self):
    def to_clause(self):
    def __init__(self, *exprs, join="??"):
    def add(self, expr):
    def count(self):
    def is_empty(self):
    def to_clause(self):
    def __init__(self, *args):
    def __init__(self, *args):
    def __init__(self, *args):
    def to_clause(self):
    def __init__(self):
    def __base_sql(cols=[], table="", joins=[], where=None, group=None, order=None, page=None):
    def cmd_select(self):
    def cmd_count(self):
    def cmd_count_unfiltered(self):
    def set_cols(self, cols):
    def add_col(self, col):
    def set_table(self, table):
    def set_joins(self, joins):
    def add_join(self, join):
    def set_where(self, where):
    def set_search(self, qsearch):
    def set_group_by(self, group):
    def add_group_by(self, group):
    def set_order_by(self, order):
    def set_page(self, page):
    def translate_query(query, cols, qfields):
    def __init__(self, results=[], total_count=0, filtered_count=0, page=None):
    def next_page(self):
    def prev_page(self):
    def page_count(self):
    def __iter__(self):
    def execute_select(db, sqlcmd):
from flask import Blueprint, Response, json, render_template, current_app, request, send_file, url_for
from flask_login import current_user, login_required
from werkzeug.urls import url_encode
from .user import User
from .job import Job 
from .auth import check_view_job, check_edit_job, can_user_edit_job, check_edit_pheno, admin_required, check_view_pheno
from .genotype import Genotype
from .phenotype import Phenotype
from .notice import Notice
from .pheno_reader import PhenoReader
from .slurm_queue import SlurmJob, get_queue
from .model_factory import ModelFactory
from .notifier import get_notifier
from .access_tracker import AccessTracker
from .db_helpers import PagedResult, PageInfo, QueryInfo
import os
import re
import gzip
import uuid
import tabix
import hashlib
import shutil
import collections
import sys, traceback
import subprocess
import requests
import numpy as np
        else:
            return send_file(output_file, as_attachment=as_attach, mimetype=mimetype)
    except Exception as e:
        print(e)
        return "File Not Found", 404

@api.route("/jobs/<job_id>/tables/top", methods=["GET"])
@check_view_job
def get_job_tophits(job_id, job=None):
    return get_job_output(job, "tophits.json", False)

@api.route("/jobs/<job_id>/plots/qq", methods=["GET"])
@check_view_job
def get_api_job_qq(job_id, job=None):
    return get_job_output(job, "qq.json")

@api.route("/jobs/<job_id>/plots/manhattan", methods=["GET"])
@check_view_job
def get_api_job_manhattan(job_id, job=None):
    return get_job_output(job, "manhattan.json")

@api.route("/jobs/<job_id>/plots/zoom", methods=["GET"])
@check_view_job
def get_job_zoom(job_id, job=None):
    header = []
    output_filename = job.get_output_file_path()
    with gzip.open(output_filename, "rt") as f:
        header = f.readline().rstrip('\n').split('\t')
        if header[1] == "BEG":
            header[1] = "BEGIN"
        if header[1] == "POS":
            header[1] = "BEGIN"
        if header[0] == "#CHROM":
            header[0] = "CHROM"
        if header[0] == "CHR":
            header[0] = "CHROM"
        if len(header)>6 and header[6] == "AF_Allele2":
            header[6] = "MAF"
        if len(header)>7 and header[7] == "N":
            header[7] = "NS"
        if len(header)>11 and header[11] == "p.value":
            header[11] = "PVALUE"
        if len(header)>12 and header[12] == "p.value":
            header[12] = "PVALUE"
    assert len(header) > 0
    chrom = request.args.get("chrom", "")
    start_pos = int(request.args.get("start_pos", "0"))
    end_pos = int(request.args.get("end_pos", "0"))

    if chrom == "":
        return ApiResult(None, header={"variant_columns": header})

    headerpos = {x:i for i,x in enumerate(header)}
    tb = tabix.open(output_filename)
    try:
        results = tb.query(chrom, start_pos, end_pos)
    except:
        if chrom.startswith("chr"):
            chrom = chrom.replace("chr","")
        else:
            chrom = "chr" + chrom
        results = tb.query(chrom, start_pos, end_pos)

    json_response_data = dict()

    json_response_data["CHROM"] = []
    json_response_data["BEGIN"] = []
    json_response_data["MARKER_ID"] = []
    json_response_data["PVALUE"] = []
    if "END" in headerpos:
        json_response_data["END"] = []
    if "NS" in headerpos:
        json_response_data["NS"] = []
    if "MAF" in headerpos:
        json_response_data["MAF"] = []
    if "BETA" in headerpos:
        json_response_data["BETA"] = []
    for r in results:
        if r[headerpos["PVALUE"]] != "NA":
            json_response_data["CHROM"].append(r[headerpos["CHROM"]])
            json_response_data["BEGIN"].append(int(r[headerpos["BEGIN"]]))
            if "END" in headerpos:
                json_response_data["END"].append(r[headerpos["END"]])
            if "MARKER_ID" in headerpos:
                json_response_data["MARKER_ID"].append(r[headerpos["MARKER_ID"]])
            else:
                var1 = "{}:{}".format(r[headerpos["CHROM"]], r[headerpos["BEGIN"]])
                if "Allele1" in headerpos and "Allele2" in headerpos:
                    var1 = "{}_{}/{}".format(var1, r[headerpos["Allele1"]], r[headerpos["Allele2"]])
                json_response_data["MARKER_ID"].append(var1)
            json_response_data["PVALUE"].append(r[headerpos["PVALUE"]])
            if "NS" in headerpos:
                json_response_data["NS"].append(r[headerpos["NS"]])
            if "MAF" in headerpos:
                maf = float(r[headerpos["MAF"]])
                if maf > .5:
                    maf = 1-maf
                json_response_data["MAF"].append(str(maf))
            if "BETA" in headerpos:
                json_response_data["BETA"].append(r[headerpos["BETA"]])
    return ApiResult(json_response_data, header={"variant_columns": list(json_response_data.keys())}) 

def merge_info_stats(info, info_stats):
    info_extract = re.compile(r'([A-Z0-9_]+)(?:=([^;]+))?(?:;|$)')
    matches = info_extract.findall(info)
    merged = dict()
    if "fields" in info_stats:
        merged[".fieldorder"] = info_stats["fields"]
    for match in matches:
        field = match[0]
        value = match[1]
        if "desc" in info_stats and field in info_stats["desc"]:
            stats = dict(info_stats["desc"][field])
        else:
            stats = dict()
        stats["value"] = value
        merged[field] = stats
    return merged

@api.route("/jobs/<job_id>/plots/pheno", methods=["GET"])

@check_view_job