def calculate_overlaps(pheno):
    genos = Genotype.list_all_for_user(current_user.rid)
    overlap_all = []
    for geno in genos:
        overlap = calculate_overlap(pheno, geno["id"])
        if overlap is not None:
            geno["overlap"] = overlap
            overlap_all.append(geno)
    return overlap_all 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 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() 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 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} 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_view_pheno(f):
    return splat_args(inject_pheno(can_view_pheno_page(f)), f) def check_edit_pheno(f):
    return splat_args(inject_pheno(can_edit_pheno(f)), f) def can_user_edit_pheno(user, pheno):
    if not user or not pheno:
        return False
    user_id = user.rid
    if not user.is_active():
        return False
    if user_id == pheno.user_id:
        return True
    if user.is_admin():
        return True
    return False
from flask import Blueprint, Response, json, render_template, current_app, request, send_file
from flask_login import current_user, login_required
from .api_blueprint import calculate_overlaps
from .genotype import Genotype
from .phenotype import Phenotype
from .notice import Notice
from .job import Job 
from .user import User
from .access_tracker import AccessTracker
from .auth import check_view_job, check_edit_job, can_user_edit_job, check_view_pheno, check_edit_pheno, can_user_edit_pheno

user_area = Blueprint("user", __name__,
    template_folder="templates")

@user_area.before_request
@login_required
def before_request():
    # Just here to trigger the login_required before any request
    pass

@user_area.route("/")
def index():
    phenos = Phenotype.list_all_for_user(current_user.rid)
    notices = list(Notice.list_current())
    return render_template("home.html", phenos=phenos, notices=notices)

@user_area.route("/jobs", methods=["GET"])
def get_jobs():
    return redirect(url_for("user.index"))

@user_area.route("/jobs/<job_id>", methods=["GET"])
@check_view_job
def get_job(job_id, job=None):

    pheno = Phenotype.get(job.get_phenotype_id(), current_app.config)