def withView(query, viewName, session, dbc, metadata=Base.metadata):
    ## check first if the view exist
    viewTable=None
    try:
        viewTable = Table(viewName, metadata, autoload=True, autoload_with=dbc.dbm.engine)
        print "Using view data"
        return session.query(viewTable)
    except:
        return query def createView(query, viewName, session):
    c = CreateView(viewName, query)
    session.execute(c)
    session.commit()

    #tt=view(viewName, metadata, query)
    #print 'tt',tt.compile()
    #metadata.create_all(dbc.dbm.engine, checkfirst=True)  # create if not existing def validURLDist(self, snapshot,portalid=None):
    with self.session_scope() as session:
        q= session.query(MetaResource.valid, func.count().label('count'))\
            .join(Dataset,Dataset.md5==MetaResource.md5)\
            .filter(Dataset.snapshot==snapshot)
        if portalid:
            q=q.filter(Dataset.portalid==portalid)

        q=q.group_by(MetaResource.valid)
        q=q.order_by(func.count(MetaResource.valid).desc())

        return q def statusCodeDist(self, snapshot,portalid=None):
    with self.session_scope() as session:
        q= session.query(ResourceInfo.status,func.count())\
            .join(MetaResource, ResourceInfo.uri==MetaResource.uri)\
            .join(Dataset,Dataset.md5==MetaResource.md5)\
            .filter(Dataset.snapshot==snapshot)\
            .filter(ResourceInfo.snapshot==snapshot)
        if portalid:
            q=q.filter(Dataset.portalid==portalid)

        q=q.group_by(ResourceInfo.status)
        q=q.order_by(func.count().desc())

        return q def portalSnapshotQualityDF(self, portalid, snapshot):

    q= self.portalSnapshotQuality(portalid,snapshot)
    data=None
    for r in q:
        data=row2dict(r)
        break
    d=[]

    datasets= int(data['datasets'])
    for inD in qa:
        for k , v in inD['metrics'].items():
            k=k.lower()
            # TODO what to do if metric has no value?
            if data[k] != None and data[k] != 'None':
                value=float(data[k])
                perc=int(data[k+'N'])/(datasets*1.0) if datasets>0 else 0
                c= { 'Metric':k, 'Dimension':inD['dimension'],
                     'dim_color':inD['color'], 'value':value, 'perc':perc}
                c.update(v)
                d.append(c)
    return pd.DataFrame(d) def getMetaResource(self, snapshot, portalid=None, size=None):
    with self.session_scope() as session:
        q= session.query(MetaResource)\
            .join(Dataset, Dataset.md5==MetaResource.md5)\
            .filter(Dataset.snapshot==snapshot)
        if portalid:
            q=q.filter(Dataset.portalid==portalid)
        if size:
            q=q.filter((MetaResource.size<=size) | (MetaResource.size == None))
        return q def getResourceInfos(self, snapshot, portalid=None):
    with self.session_scope() as session:
        q= session.query(ResourceInfo)\
            .join(MetaResource, ResourceInfo.uri==MetaResource.uri)\
            .join(Dataset,Dataset.md5==MetaResource.md5)\
            .filter(Dataset.snapshot==snapshot)
        if portalid:
            q=q.filter(Dataset.portalid==portalid)
        return q class TimeoutError(Exception):
class ErrorHandler():
    def __init__(self, message, timeout):
    def handleError(cls, log, msg=None, exception=None, debug=False, **kwargs):
    def printStats(cls):
def getExceptionCode(e):
def getExceptionString(e):
    DEBUG=False def hm():
def getFetchProcessChart(db, snapshot, n=3):
def getData(db, snapshot, n=3):
def fetchProcessChart(data,cnts):
def portalsScatter(df):
    def get_dataset(df, name):
def qualityChart(df):
    def rad(mic):
def evolSize(source,df):
def evolutionCharts(df):
def getWeekStringTick():
def systemEvolutionBarPlot(df, yLabel, values):
def systemEvolutionPlot(df):
def portalDynamicity(df):
    def getWeekString(yearweek):
        LINE_ARGS = dict(color=c, line_color=None) def row2dict(r):
    if hasattr(r, '_fields'):
        d = {}
        for field in r._fields:
            rf = r.__getattribute__(field)
            if isinstance(rf, Base):
                d.update(_row2dict(rf))
            else:
                d[field] = rf
        return d
    if isinstance(r, Base):
        return _row2dict(r) class Portal(Base):
    __tablename__ = tab_portals

    id      = Column(String, primary_key=True, index=True,nullable=False)
    uri     = Column(String, nullable=False)
    apiuri  = Column(String)
    software = Column(String(12), nullable=False) # OpenDataSoft, CKAN, Socrata <13
    iso     = Column(String(2), nullable=False)
    active  = Column(Boolean, default=True,nullable=False)

    snapshots = relationship("PortalSnapshot", back_populates="portal")
    snapshotsquality = relationship("PortalSnapshotQuality", back_populates="portal")

    @hybrid_property
    def snapshot_count(self):
        print len(self.snapshots)
        return len(self.snapshots)
    @snapshot_count.expression

    def snapshot_count(cls):
        return select([func.count(PortalSnapshot.snapshot)])\
            .where(PortalSnapshot.portalid == cls.id).label("snapshot_count")

    @hybrid_property
    def first_snapshot(self):
        print [s for s in self.snapshots]
        return min([s.snapshot for s in self.snapshots])

    @first_snapshot.expression
    def first_snapshot(cls):
        return select([func.min(PortalSnapshot.snapshot)])\
            .where(PortalSnapshot.portalid == cls.id).label("first_snapshot")

    @hybrid_property
    def last_snapshot(self):
        return max([s.snapshot for s in self.snapshots])

    @last_snapshot.expression
    def last_snapshot(cls):
        return select([func.max(PortalSnapshot.snapshot)])\
            .where(PortalSnapshot.portalid == cls.id).label("last_snapshot")

    @hybrid_property
    def datasetcount(self):
        return self.snapshots.order_by(PortalSnapshot.snapshot.desc()).one().datasetcount

    @datasetcount.expression
    def datasetcount(cls):
        q=select([PortalSnapshot.datasetcount])\
            .where(PortalSnapshot.portalid == cls.id).order_by(PortalSnapshot.snapshot.desc()).limit(1).label("datasetcount")
        return q

    @hybrid_property
    def resourcecount(self):
        return self.snapshots.order_by(PortalSnapshot.snapshot.desc()).one().resourcecount

    @resourcecount.expression
    def resourcecount(cls):
        q=select([PortalSnapshot.resourcecount])\
            .where(PortalSnapshot.portalid == cls.id).order_by(PortalSnapshot.snapshot.desc()).limit(1).label("resourcecount")
        return q

    def __repr__(self):
        return "<Portal(id=%s, uri='%s', apiuri='%s', software='%s', iso=%s)>" % (
            self.id, self.uri, self.apiuri, self.software, self.iso) class PortalSnapshotQuality(Base):
    __tablename__ = tab_portalsnapshotquality

    portalid      = Column(String, ForeignKey(tab_portals+'.id'), primary_key=True, index=True,nullable=False)
    snapshot= Column( SmallInteger, primary_key=True)
    portal  = relationship("Portal", back_populates="snapshotsquality")

    cocu = Column(Float)
    cocuN = Column(Integer)
    coce = Column(Float)
    coceN = Column(Integer)
    coda = Column(Float)
    codaN = Column(Integer)
    cofo = Column(Float)
    cofoN = Column(Integer)
    coli = Column(Float)
    coliN = Column(Integer)
    coac = Column(Float)
    coacN = Column(Integer)
    exda = Column(Float)
    exdaN = Column(Integer)
    exri = Column(Float)
    exriN = Column(Integer)
    expr = Column(Float)
    exprN = Column(Integer)
    exac = Column(Float)
    exacN = Column(Integer)
    exdi = Column(Float)
    exdiN = Column(Integer)
    exte = Column(Float)
    exteN = Column(Integer)
    exsp = Column(Float)
    exspN = Column(Integer)
    exco = Column(Float)
    excoN = Column(Integer)
    opfo = Column(Float)
    opfoN = Column(Integer)
    opma = Column(Float)
    opmaN = Column(Integer)
    opli = Column(Float)
    opliN = Column(Integer)
    datasets=Column(Integer)

    def __repr__(self):
        return "<PortalSnapshotQuality(id=%s, snapshot=%s, agg=%s)>" % (
            self.portalid, self.snapshot,  any([self.exda,self.coac,self.coce,self.cocu])) class PortalSnapshot(Base):
    __tablename__ = tab_portalsnapshot

    portalid      = Column(String, ForeignKey(tab_portals+'.id'), primary_key=True, index=True,nullable=False)
    snapshot= Column( SmallInteger, primary_key=True)
    portal  = relationship("Portal", back_populates="snapshots")

    start       = Column(TIMESTAMP)
    end         = Column(TIMESTAMP)
    status      = Column(SmallInteger)
    exc         = Column(String)
    datasetcount    = Column(Integer)
    datasetsfetched    = Column(Integer)
    resourcecount   = Column(Integer)

    @hybrid_property
    def fetchtime(self):
        return self.end-self.start

    datasets = relationship("Dataset", back_populates="portalsnapshot")

    def __repr__(self):
        return "<PortalSnapshot(id=%s, snapshot=%s, start=%s, end=%s, status=%s,ds=%s,res=%s)>" % (
            self.portalid, self.snapshot, self.start, self.end, self.status,self.datasetcount,self.resourcecount) class Dataset(Base):
    __tablename__ = tab_datasets

    id           = Column( String, primary_key=True)
    snapshot     = Column( SmallInteger, primary_key=True, index=True)
    portalid     = Column( String, primary_key=True, index=True)
    organisation = Column(String, index=True)
    title        = Column(String, index=True)
    md5          = Column(String, ForeignKey(tab_datasetsdata+'.md5'), index=True)


    __table_args__ = (ForeignKeyConstraint([portalid, snapshot],
                                           [tab_portalsnapshot+'.portalid',tab_portalsnapshot+'.snapshot']),
                      {})

    portalsnapshot  = relationship("PortalSnapshot", back_populates="datasets")
    data  = relationship("DatasetData", back_populates="dataset")

    def __repr__(self):
        return "<Dataset(id=%s, portalid='%s', snapshot=%s, md5=%s)>" % (
            self.id, self.portalid, self.snapshot, self.md5) class DatasetData(Base):
    __tablename__ = tab_datasetsdata

    md5 = Column(String, primary_key=True, index=True, nullable=False)
    raw = Column(JSONB)
    dataset  = relationship("Dataset", back_populates="data")
    resources  = relationship("MetaResource", back_populates="dataset")

    modified = Column(TIMESTAMP)
    created = Column(TIMESTAMP)
    organisation = Column(String, index=True)
    license = Column(String, index=True)

    def __repr__(self):
        return "<DatasetData(md5=%s, data=%s)>" % (
            self.md5, self.raw is not None) class DatasetQuality(Base):
    __tablename__ = tab_datasetsquality

    md5     = Column(String, ForeignKey(DatasetData.md5), primary_key=True, index=True)
    cocu = Column(Boolean)
    coce = Column(Boolean)
    coda = Column(Float)
    cofo = Column(Float)
    coli = Column(Boolean)
    coac = Column(Boolean)

    exda = Column(Float)
    exri = Column(Float)
    expr = Column(Float)
    exac = Column(Boolean)
    exdi = Column(Float)
    exte = Column(Float)
    exsp = Column(Float)
    exco = Column(Boolean)

    opfo = Column(Float)
    opma = Column(Float)
    opli = Column(Boolean)


    data = relationship("DatasetData", backref=backref("quality", uselist=False))

    def __repr__(self):
        return "<DatasetQuality(md5=%s, assessment=%s)>" % (
            self.md5, any([self.exda,self.coac,self.coce,self.cocu])) class MetaResource(Base):
    __tablename__ = tab_resources

    uri = Column(String, primary_key=True, index=True)
    md5 = Column(String,ForeignKey(DatasetData.md5), primary_key=True,index=True )
    valid = Column(Boolean)
    format = Column(String)
    media = Column(String)
    size = Column(BigInteger)
    created = Column(TIMESTAMP)
    modified = Column(TIMESTAMP)


    dataset  = relationship("DatasetData", back_populates="resources")


    def info(cls):
        return select([ResourceInfo]).where(cls.uri == ResourceInfo.uri).where(cls.snapshot== ResourceInfo.snapshot)

    def __repr__(self):
        return "<Resource(uri=%s, dataset=%s)>" % (
            self.uri, self.md5) class ResourceInfo(Base):
    __tablename__ = tab_resourcesinfo

    uri= Column(String, primary_key=True)
    snapshot= Column(SmallInteger, primary_key=True)
    timestamp= Column(TIMESTAMP)
    status=Column(SmallInteger)
    exc=Column(String)
    header=Column(JSONB)
    mime=Column(String)
    size=Column(BigInteger) class PortalSnapshotDynamicity(Base,Serializable):
    __tablename__ = tab_portalsnapshotdynamicity

    portalid      = Column(String, ForeignKey(tab_portals+'.id'), primary_key=True, index=True,nullable=False)
    snapshot= Column( SmallInteger, primary_key=True)


    updated = Column(Integer)
    added = Column(Integer)
    deleted = Column(Integer)
    static =  Column(Integer)
    intersected =  Column(Integer)
    dindex = Column(Integer)
    changefrequ = Column(Float)
    size = Column(Integer)

    @hybrid_property
    def dyratio(self):
        return (self.added+self.deleted+self.updated)\
               /(1.0* self.intersected) if self.intersected >0 else 0

    @hybrid_property
    def adddelratio(self):
        return (self.added-self.deleted)\
               /(1.0* (self.added+self.deleted))if ((self.added+self.deleted)) >0 else 0

    @hybrid_property
    def addRatio(self):
        return (self.added) \
               / (1.0 * self.intersected)if self.intersected >0 else 0

    @hybrid_property
    def delRatio(self):
        return (self.deleted) \
               / (1.0 * self.intersected)if self.intersected >0 else 0

    @hybrid_property
    def updatedRatio(self):
        return ( self.updated) \
               / (1.0 * self.intersected)if self.intersected >0 else 0

    @hybrid_property
    def staticRatio(self):
        return (self.static) \
               / (1.0 * self.intersected)if self.intersected >0 else 0 def aggregatePortalInfo(session, portalid, snapshot, dbc, limit=3):
    stats={}
    with Timer(key=portalid+'-agg', verbose=True):
        ds= session.query(Dataset).filter(Dataset.snapshot==snapshot).filter(Dataset.portalid==portalid).count()
        rs = session.query(PortalSnapshot.resourcecount).filter(PortalSnapshot.portalid==portalid).filter(PortalSnapshot.snapshot==snapshot).first()
        print
        #print 'dsCount', ds
        #TODO fix resource count
        for key, cFunc, dFunc in [
                        ('organisation',organisationDist, distinctOrganisations)
                        ,('license',licenseDist,distinctLicenses)
                        ,('format', formatDist, distinctFormats)
                         ]:
            if key=='format':
                total=row2dict(rs)['resourcecount']
            else:
                total=ds
            with Timer(key='query_{}-{}'.format(portalid,key), verbose=True):

                s=[]

                viewName = "view_{}_{}_{}".format(key, portalid,snapshot)
                qorg=cFunc(session,snapshot,portalid=portalid)
                q = withView(qorg, viewName, session, dbc)
                start=time.time()
                if limit:
                    q=q.limit(limit)
                else:
                    q=q.all()
                for i in q:
                    d=row2dict(i)
                    #print d
                    d['perc']=d['count']/(1.0*total) if total>0 else 0
                    s.append(d)
                t=sum(item['count'] for item in s)
                #print key, 'total',t
                if ds-t != 0:
                    s.append({key:'Others', 'count':total-t,'perc':(total-t)/(1.0*total)})
                end=time.time()
                if (end-start)>5:
                    log.info("Create View {}".format(viewName))
                    createView(qorg, viewName,session)
                #q = withView(qorg, viewName, session, dbc)
                stats[key]={'distinct':dFunc(session,snapshot,portalid=portalid).count(),'top3Dist':s}

    return stats class Timer(object):

    measures={}

    def __init__(self, verbose=False, key=None):
        self.verbose = verbose
        self.key=key

    def __enter__(self):
        self.start = time.time()
        return self

    def __exit__(self, *args):
        end = time.time()
        secs = end - self.start
        msecs = secs * 1000  # millisecs
        if self.verbose:
            print '(%s) elapsed time: %f ms' % (self.key,msecs)
        if self.key:
            if self.key not in self.__class__.measures:
                self.__class__.measures[self.key]=faststat.Stats()
            if msecs>=0:
                self.__class__.measures[self.key].add(msecs)


    @classmethod
    def printStats(cls):
        print '>>>','--*'*10,'TIMER','*--'*10
        s=[" Timing stats:","\n"]
        for m,st in Timer.measures.items():
            p = st.percentiles
            if st.n < len(p):
                quartiles = "(n too small)"
            else:
                quartiles = (_sigfigs(p.get(0.25, -1)),
                    _sigfigs(p.get(0.5, -1)), _sigfigs(p.get(0.75, -1)))
            d = ["  ["+m+'] -', str(st.mean), 'avg ms for',m,str(st.n),'calls)'
                    ,"\n       (min:",str(st.min),"-",str(st.max),":max, quantils:",quartiles,")\n"]
            s=s+d
        s=s+['-'*50]
        print " ".join(s)
        print '<<<','--*'*10,'TIMER','*--'*10

    @classmethod
    def getStats(cls):

        stats={}
        for m,st in cls.measures.items():
            p = st.percentiles
            stats[m]={
                'avg':st.mean
                , 'calls':st.n
                , 'min':st.min
                , 'max':st.max
                , 'q25':None, 'q5':None, 'q75':None
            }
            if st.n < len(p):
                quartiles = "(n too small)"
            else:
                stats[m]['q25']=_sigfigs(p.get(0.25, -1))
                stats[m]['q5']=_sigfigs(p.get(0.5, -1))
                stats[m]['q75']=_sigfigs(p.get(0.75, -1))
        return stats def getWeekString(yearweek):
    if yearweek is None or len(str(yearweek))==0:
        return ''
    first=tofirstdayinisoweek(yearweek)
    last=toLastdayinisoweek(yearweek)
    return "{} {} - {} {}, {}".format(first.strftime("%b"), first.day,last.strftime("%b"),last.day,last.year) def getSnapshotfromTime(now, delta=None,before=False):
    """
    Returns the snapshot for a given time (+/- a delta)
    :param now:
    :param delta: current time +/- delta
    :param before:
    :return: YYWW
    """
    if delta:
        if before:
            now -= delta
        else:
            now += delta

    y=now.isocalendar()[0]
    w=now.isocalendar()[1]
    sn=str(y)[2:]+'{:02}'.format(w)
    return int(sn) def getPreviousWeek(snapshot):
    d = tofirstdayinisoweek(snapshot)
    prev = getSnapshotfromTime(d, timedelta(days=7), before=True)
    if snapshot == prev:
        return prev-1 def getNextWeek(snapshot):
    d = tofirstdayinisoweek(snapshot)

    return getSnapshotfromTime(d, timedelta(days=7), before=False) def getLastNSnapshots(yearweek,n):
    d=[]
    sn=yearweek
    while n>0:
        sn=getPreviousWeek(sn)
        d.append(sn)
        n=n-1
    return d def getCurrentSnapshot():
    now = datetime.now()
    y=now.isocalendar()[0]
    w=now.isocalendar()[1]
    sn=str(y)[2:]+'{:02}'.format(w)

    return sn def report(datasetdata, datasetquality, software): def orgaReport(Session, portal, snapshot, orga, contact=None):
    with Timer(key=orga, verbose=True):
        q = Session.query(Dataset) \
            .filter(Dataset.portalid == portal.id) \
            .filter(Dataset.snapshot == snapshot) \
            .filter(Dataset.organisation == orga) \
            .join(DatasetData, DatasetData.md5 == Dataset.md5) \
            .join(DatasetQuality, DatasetQuality.md5 == Dataset.md5) \
            .add_entity(DatasetData).add_entity(DatasetQuality)
        pereMail = {}
        uris=set([])
        summary={'status':defaultdict(int)}
        summary['status'][200]=0
        summary['status'][404]=0
        summary['status']['total'] = 0

        for res in q: #Dataset, DatasetData, DatasetQuality
            ds={}
            ds['dataset'] = row2dict(res[0])
            #ds['dataset']['external_uri']=portal.apiuri+"/katalog/dataset"
            ds['data'] = row2dict(res[1])
            ds['quality']=row2dict(res[2])


            d = portal_fetch_processors.Dataset(snapshot=snapshot, portalID=portal.id, did=ds['dataset']['id'], data=ds['data']['raw'],
                                                status=200, software=portal.software)
            d.dcat = dict_to_dcat(ds['data']['raw'], portal)
            contactInfo = getContactPointValues(d)
            if len(contactInfo) > 1:
                if contact is not None and contact!=contactInfo[1]:
                    continue

                ds['report'] = dataset_reporter.report(res[1], res[2], portal.software)

                orgas = pereMail.setdefault(contactInfo[1], {})
                ds_list = orgas.setdefault(orga, [])
                ds_list.append(ds)
                ds['resourcesStatus']=defaultdict(int)
                ds['resourcesStatus']['total']=0
                ds['resources']=[row2dict(r) for r in res[1].resources]
                for resou in ds['resources']:
                    resri=Session.query(ResourceInfo).filter(ResourceInfo.uri == resou['uri']).filter(ResourceInfo.snapshot==snapshot).first()
                    if resri is not None:
                        resou['info']=row2dict(resri)
                        ds['resourcesStatus'][resou['info']['status']]+=1
                        ds['resourcesStatus']['total'] += 1
                        if resou['uri'] not in uris:
                            summary['status'][resou['info']['status']]+=1
                            summary['status']['total'] += 1
                    if resou['uri'] not in uris:
                        uris.add(resou['uri'])

                ds['resourcesStatus']=dict(ds['resourcesStatus'])
        ContactCount = 0
        #print "  Organisation:", orga
        for k, v in pereMail.items():
            print "  contact:", k
            for orga, ds_list in v.items():
                print "   ", orga, len(ds_list)
                ContactCount += len(ds_list)
                for ds in ds_list:
                    print "    >",ds['report']

        summary['status']=dict(summary['status'])
        pereMail['summary']=summary
        pereMail['summary']['totaluris']=len(uris)
        return pereMail def contactPerOrga(Session, portal, snapshot, orga):
    q = Session.query(Dataset) \
        .filter(Dataset.portalid == portal.id) \
        .filter(Dataset.snapshot == snapshot) \
        .filter(Dataset.organisation == orga) \
        .join(DatasetData, DatasetData.md5 == Dataset.md5) \
        .join(DatasetQuality, DatasetQuality.md5 == Dataset.md5) \
        .add_entity(DatasetData).add_entity(DatasetQuality)
    pereMail = set([])
    for res in q:  # Dataset, DatasetData, DatasetQuality
        ds = row2dict(res)

        d = portal_fetch_processors.Dataset(snapshot=snapshot, portalID=portal.id, did=ds['id'], data=ds['raw'],
                                            status=200, software=portal.software)
        d.dcat = dict_to_dcat(ds['raw'], portal)
        contact = getContactPointValues(d)
        if len(contact) > 1:
            pereMail.add(contact[1])
    return pereMail DCAT = Namespace("http://www.w3.org/ns/dcat#")
FOAF = Namespace("http://xmlns.com/foaf/0.1/")
VCARD = Namespace("http://www.w3.org/2006/vcard/ns#")
def resp_party(g, doc, orga):
def convert(portal, data):
import ast
import datetime
import json
import time
import StringIO
import csv
import jinja2
import pandas as pd
from collections import defaultdict
from urlparse import urlparse
from bokeh.embed import components
from bokeh.resources import INLINE
from flask import Blueprint, current_app, render_template, jsonify, make_response, url_for
from markupsafe import Markup
from sqlalchemy import func, and_
from odpw.core.mat_views import withView, createView
from odpw.core.api import validURLDist, statusCodeDist, portalSnapshotQualityDF, getMetaResource, getResourceInfos
from odpw.utils.error_handling import errorStatus
from odpw.utils.plots import fetchProcessChart, qualityChart, qa, portalsScatter, evolutionCharts, \
    systemEvolutionPlot, portalDynamicity
from odpw.core.db import row2dict
from odpw.core.model import Portal, PortalSnapshotQuality, PortalSnapshot, Dataset, DatasetData, DatasetQuality, \
    MetaResource, ResourceInfo, PortalSnapshotDynamicity
from odpw.services.aggregates import aggregatePortalInfo
from odpw.utils.timing import Timer
from odpw.utils.utils_snapshot import getWeekString, getSnapshotfromTime, getPreviousWeek, getNextWeek, \
    getLastNSnapshots, getCurrentSnapshot
from odpw.web_rest.cache import cache
from odpw.reporter import dataset_reporter
from odpw.reporter.contact_reporter import orgaReport, contactPerOrga
from schemadotorg import dcat_to_schemadotorg
            else:
                viewName = "view_{}_{}_{}_{}".format('resstatus', portalid, snapshot, orga)

            qorg = statusCodeDist(session,snapshot,portalid=portalid, orga=orga)
            q = withView(qorg, viewName, session, dbc)
            start = time.time()
            for res in q:
                data['status'][res[0]]=res[1]
            end = time.time()
            if (end - start) > 5:
                print("Create View {}".format(viewName))
                createView(qorg, viewName, session)

        return {'resourcesInfo':data}


# @ui.route('/portal/<portalid>/<int:snapshot>', methods=['GET'])
# @cache.cached(timeout=60*60*24)
# def portal(snapshot, portalid):
#     with Timer(key="get_portal",verbose=True):
#
#         Session=current_app.config['dbsession']
#         dbc=current_app.config['dbc']
#         data=getPortalInfos(Session,portalid,snapshot)
#
#         with Timer(key="query_portal",verbose=True):
#             r=Session.query(Portal, Portal.datasetcount, Portal.resourcecount).filter(Portal.id==portalid)
#             for P in r:
#                 data.update(row2dict(P[0]))
#                 data['datasets']=P[1]
#                 data['resources']=P[2]
#         with Timer(key="query_portal_agg", verbose=True):
#             data.update(aggregatePortalInfo(Session,portalid,snapshot,dbc))
#         return render("odpw_portal.jinja",  snapshot=snapshot, portalid=portalid,data=data)

@ui.route('/portal/<portalid>/', methods=['GET'])
@ui.route('/portal/<portalid>/<int:snapshot>', methods=['GET'])
@cache.cached(timeout=60*60*24)
def portal(portalid,snapshot=getSnapshotfromTime(datetime.datetime.now())):
    with Timer(key="get_portal",verbose=True):
        current_sn = snapshot
        Session=current_app.config['dbsession']
        data=getPortalInfos(Session,portalid,snapshot)
        dynamicityEnabled = current_app.config.get('dynamicity', False)


        with Timer(key="query_portal",verbose=True):
            q = Session.query(Portal).filter(Portal.id == portalid) \
                .join(PortalSnapshotQuality, PortalSnapshotQuality.portalid == Portal.id) \
                .filter(PortalSnapshotQuality.snapshot == snapshot) \
                .join(PortalSnapshot, PortalSnapshot.portalid == Portal.id) \
                .filter(PortalSnapshot.snapshot == snapshot) \
                .add_entity(PortalSnapshot) \
                .add_entity(PortalSnapshotQuality)

            if dynamicityEnabled:
                q = q.join(PortalSnapshotDynamicity, PortalSnapshotDynamicity.portalid == Portal.id) \
                    .filter(PortalSnapshotDynamicity.snapshot == snapshot) \
                    .add_entity(PortalSnapshotDynamicity)
            r = q.first()
            while r is None:
                snapshot= getPreviousWeek(snapshot)
                q = Session.query(Portal).filter(Portal.id == portalid) \
                    .join(PortalSnapshotQuality, PortalSnapshotQuality.portalid == Portal.id) \
                    .filter(PortalSnapshotQuality.snapshot == snapshot) \
                    .join(PortalSnapshot, PortalSnapshot.portalid == Portal.id) \
                    .filter(PortalSnapshot.snapshot == snapshot) \
                    .add_entity(PortalSnapshot) \
                    .add_entity(PortalSnapshotQuality)

                if dynamicityEnabled:
                    q = q.join(PortalSnapshotDynamicity, PortalSnapshotDynamicity.portalid == Portal.id) \
                        .filter(PortalSnapshotDynamicity.snapshot == snapshot) \
                        .add_entity(PortalSnapshotDynamicity)
                r = q.first()

            data['portal'] = row2dict(r[0])
            data['fetchInfo'] = row2dict(r[1])
            data['fetchInfo']['duration']=data['fetchInfo']['end']-data['fetchInfo']['start']

            if dynamicityEnabled:
                data['dynamicity'] = row2dict(r[3])
            data['quality'] = row2dict(r[2])


        #with Timer(key="query_portal_agg", verbose=True):
        #    data.update(aggregatePortalInfo(Session,portalid,snapshot,dbc))
        return render("odpw_portal.jinja",  snapshot=current_sn, portalid=portalid,data=data)

@ui.route('/portal/<portalid>/<int:snapshot>/report', methods=['GET'])
#@cache.cached(timeout=60*60*24)
def portalreport(portalid,snapshot=getSnapshotfromTime(datetime.datetime.now())):
    with Timer(key="get_portal",verbose=True):

        Session=current_app.config['dbsession']
        data=getPortalInfos(Session,portalid,snapshot)
        with Timer(key="query_portalreport",verbose=True):
            q = Session.query(Dataset.organisation) \
                .filter(Dataset.portalid == portalid) \
                .filter(Dataset.snapshot == snapshot).distinct(Dataset.organisation)

            data['organisations']=[ row2dict(res) for res in q]
        return render("odpw_portal_report.jinja",  snapshot=snapshot, portalid=portalid,data=data)

@ui.route('/portal/<portalid>/<int:snapshot>/report/<orga>', methods=['GET'])
#@cache.cached(timeout=60*60*24)
def portalOrgareport(portalid,orga,snapshot=getSnapshotfromTime(datetime.datetime.now())):
    with Timer(key="get_portal",verbose=True):

        Session=current_app.config['dbsession']
        data=getPortalInfos(Session,portalid,snapshot)

        with Timer(key="query_portalreport",verbose=True):
            portal=Session.query(Portal).filter(Portal.id==portalid).first()
            data['contacts']=contactPerOrga(Session, portal, snapshot, orga)

        return render("odpw_portal_report_contacts.jinja",  snapshot=snapshot, portalid=portalid,data=data,organisation=orga)


@ui.route('/portal/<portalid>/<int:snapshot>/report/<contact>/<orga>', methods=['GET'])

def portalOrgareportContact(portalid,contact,orga,snapshot=getSnapshotfromTime(datetime.datetime.now())):