def post(self):
     if not self.current_user:
         return self.send_error(403)
     self.requestid = pushmanager.core.util.get_int_arg(self.request, 'id')
     self.pushid = pushmanager.core.util.get_int_arg(self.request, 'push')
     select_query = db.push_pushes.select().where(
         db.push_pushes.c.id == self.pushid,
     )
     update_query = db.push_requests.update().where(SA.and_(
         db.push_requests.c.state == 'staged',
         db.push_requests.c.id == self.requestid,
         SA.exists(
             [1],
             SA.and_(
                 db.push_pushcontents.c.push == self.pushid,
                 db.push_pushcontents.c.request == self.requestid,
             )
         ))).values({
             'state': 'verified',
         })
     finished_query = db.push_requests.select().where(SA.and_(
         db.push_requests.c.state == 'staged',
         SA.exists(
             [1],
             SA.and_(
                 db.push_pushcontents.c.push == self.pushid,
                 db.push_pushcontents.c.request == db.push_requests.c.id,
             )
         )))
     db.execute_transaction_cb([select_query, update_query, finished_query], self.on_db_complete)

    def get_next_to_translate(self, session):
        """ Use the milestones and priority to find the next description to translate """
        # This is the query we want:
        # select description_id from (
        #         select description_id, 50 as score from languages_tb join description_milestone_tb on (milestone_high=milestone) where language = 'nl'
        # union all
        #         select description_id, 30 from languages_tb join description_milestone_tb on (milestone_medium=milestone) where language = 'nl'
        # union all
        #         select description_id, 10 from languages_tb join description_milestone_tb on (milestone_low=milestone) where language = 'nl'
        # union all
        #         select description_id, prioritize from description_tb
        # ) x
        # where not exists (select 1 from translation_tb where translation_tb.description_id = x.description_id)
        # group by description_id order by sum(score) desc
        # limit 1;
        lang_cte = session.query(Languages).filter_by(language=self.language).cte("language")

        prio = session.query(Description.description_id, Description.prioritize)
        high = session.query(DescriptionMilestone.description_id, literal(50).label("prioritize")).join(lang_cte, lang_cte.c.milestone_high==DescriptionMilestone.milestone)
        medium = session.query(DescriptionMilestone.description_id, literal(30).label("prioritize")).join(lang_cte, lang_cte.c.milestone_medium==DescriptionMilestone.milestone)
        low = session.query(DescriptionMilestone.description_id, literal(10).label("prioritize")).join(lang_cte, lang_cte.c.milestone_low==DescriptionMilestone.milestone)

        prio_cte = union_all(prio, high, medium, low).cte()

        q = session.query(prio_cte.c.description_tb_description_id). \
                    filter(~exists([1], Translation.description_id == prio_cte.c.description_tb_description_id)). \
                    filter(~exists([1], PendingTranslation.description_id == prio_cte.c.description_tb_description_id)). \
                    group_by(prio_cte.c.description_tb_description_id). \
                    order_by(func.sum(prio_cte.c.description_tb_prioritize).desc())

        row = q.first()
        if row:
            return row[0]

 def total_standard(cls, person, peroid):
     return select([func.sum(Standard.money).label('money')]).where(
         exists().where(and_(
             Standard.id == cls.standard_id,
             exists().where(and_(
                 cls.person_id == person.id,
                 cls.effective_before(peroid)))))).c.money

def upgrade():
    conn = op.get_bind()

    l = sa.table('language', *map(sa.column, ['pk', 'id', 'name']))
    ccols = ['created', 'updated', 'active', 'id', 'name', 'continent']
    c = sa.table('country', *map(sa.column, ['pk'] + ccols))
    lccols = ['created', 'updated', 'active', 'language_pk', 'country_pk']
    lc = sa.table('countrylanguage', *map(sa.column, lccols))

    lwhere = (l.c.id == sa.bindparam('id_'))

    cid, cname, ccont = map(sa.bindparam, ['cc', 'name', 'continent'])
    cwhere = (c.c.id == cid)

    insert_c = c.insert(bind=conn).from_select(ccols,
        sa.select([sa.func.now(), sa.func.now(), True, cid, cname, ccont])
        .where(~sa.exists().where(cwhere)))

    liwhere = sa.exists()\
        .where(lc.c.language_pk == l.c.pk).where(lwhere)\
        .where(lc.c.country_pk == c.c.pk).where(cwhere)

    unlink_country = lc.delete(bind=conn).where(liwhere)

    l_pk = sa.select([l.c.pk]).where(lwhere).as_scalar()
    c_pk = sa.select([c.c.pk]).where(cwhere).as_scalar()

    link_country = lc.insert(bind=conn).from_select(lccols,
        sa.select([sa.func.now(), sa.func.now(), True, l_pk, c_pk])
        .where(~liwhere))

    insert_c.execute(cc=AFTER, name=NAME, continent=CONTINENT)
    for id_ in IDS:
        unlink_country.execute(id_=id_, cc=BEFORE)
        link_country.execute(id_=id_, cc=AFTER)

    def merge_relations(
            table: model.Base, source_tag_id: int, target_tag_id: int) -> None:
        alias1 = table
        alias2 = sa.orm.util.aliased(table)
        update_stmt = (
            sa.sql.expression.update(alias1)
            .where(alias1.parent_id == source_tag_id)
            .where(alias1.child_id != target_tag_id)
            .where(
                ~sa.exists()
                .where(alias2.child_id == alias1.child_id)
                .where(alias2.parent_id == target_tag_id))
            .values(parent_id=target_tag_id))
        db.session.execute(update_stmt)

        update_stmt = (
            sa.sql.expression.update(alias1)
            .where(alias1.child_id == source_tag_id)
            .where(alias1.parent_id != target_tag_id)
            .where(
                ~sa.exists()
                .where(alias2.parent_id == alias1.parent_id)
                .where(alias2.child_id == target_tag_id))
            .values(child_id=target_tag_id))
        db.session.execute(update_stmt)

def upgrade():
    conn = op.get_bind()

    cocols = [
        'created', 'updated', 'active', 'version', 'polymorphic_type',
        'id', 'name',
    ]
    co = sa.table('contribution', *map(sa.column, ['pk'] + cocols))
    chcols = ['pk', 'sortkey']
    ch = sa.table('chapter', *map(sa.column, chcols))

    id_, name = map(sa.bindparam, ['id_', 'name'])

    cowhere = [co.c.id == id_, co.c.name == name]

    insert_co = co.insert(bind=conn).from_select(cocols,
        sa.select([sa.func.now(), sa.func.now(), True, 1, sa.literal('custom'), id_, name])
        .where(~sa.exists().where(sa.or_(*cowhere))))

    co_pk = sa.select([co.c.pk]).where(sa.and_(*cowhere)).as_scalar()
    sortkey = sa.bindparam('sortkey')

    insert_ch = ch.insert(bind=conn).from_select(chcols,
        sa.select([co_pk, sortkey])
        .where(~sa.exists().where(ch.c.pk == co.c.pk).where(sa.or_(*cowhere)))
        .where(sa.exists().where(sa.and_(*cowhere))))

    insert_co.execute(id_=ID, name=NAME)
    insert_ch.execute(id_=ID, name=NAME, sortkey=SORTKEY)

    def whereInEquipement(self,fullQueryJoin,criteria):
        sensorObj = list(filter(lambda x:'FK_Sensor'==x['Column'], criteria))[0]
        sensor = sensorObj['Value']

        table = Base.metadata.tables['MonitoredSiteEquipment']
        joinTable = outerjoin(table,Sensor, table.c['FK_Sensor'] == Sensor.ID)

        if sensorObj['Operator'].lower() in ['is','is not'] and sensorObj['Value'].lower() == 'null':
            subSelect = select([table.c['FK_MonitoredSite']]
                ).select_from(joinTable).where(
                and_(MonitoredSite.ID== table.c['FK_MonitoredSite']
                    ,or_(table.c['EndDate'] >= func.now(),table.c['EndDate'] == None)
                        ))
            if sensorObj['Operator'].lower() == 'is':
                fullQueryJoin = fullQueryJoin.where(~exists(subSelect))
            else :
                fullQueryJoin = fullQueryJoin.where(exists(subSelect))
        else :
            subSelect = select([table.c['FK_MonitoredSite']]
                ).select_from(joinTable).where(
                and_(MonitoredSite.ID== table.c['FK_MonitoredSite']
                    ,and_(eval_.eval_binary_expr(Sensor.UnicIdentifier,sensorObj['Operator'],sensor)
                        ,or_(table.c['EndDate'] >= func.now(),table.c['EndDate'] == None))
                        ))
            fullQueryJoin = fullQueryJoin.where(exists(subSelect))
        return fullQueryJoin

def run():
    
    
    activecnt = meta.Session.query(
                NotamUpdate.appearnotam).filter(
                    NotamUpdate.disappearnotam==None).distinct().subquery()
    
    now=datetime.utcnow()
    cutoff=now-timedelta(days=14)
    
    
    ncnt=meta.Session.query(Notam).filter(
                    sa.and_(
                        sa.not_(sa.exists().where(sa.and_(
                            Notam.ordinal==NotamUpdate.appearnotam,
                            NotamUpdate.disappearnotam==None))),
                        Notam.downloaded<cutoff
                    )).delete(False)

    ucnt=meta.Session.query(NotamUpdate).filter(
                    sa.and_(
                        sa.exists().where(
                            sa.and_(
                                Notam.downloaded<cutoff,
                                Notam.ordinal==NotamUpdate.disappearnotam)),
                        NotamUpdate.disappearnotam!=None
                    )).delete(False)
    print "Deleted %d notams, and %d notamupdates"%(ncnt,ucnt)
    meta.Session.flush()
    meta.Session.commit()

    def has_property(cls, prop, when=None):
        # TODO Use joins
        property_granted_select = select(
            [null()],
            from_obj=[
                Property.__table__,
                PropertyGroup.__table__,
                Membership.__table__
            ]
        ).where(
            and_(
                Property.name == prop,
                Property.property_group_id == PropertyGroup.id,
                PropertyGroup.id == Membership.group_id,
                Membership.user_id == cls.id,
                Membership.active(when)
            )
        )
        #.cte("property_granted_select")
        return and_(
            not_(exists(
                property_granted_select.where(
                    Property.granted == false())

            )),
            exists(
                property_granted_select.where(
                    Property.granted == true()
                )
            )
        ).self_group().label("has_property_" + prop)

    def countFilterOnDynProp(self,fullQuery,criteria):
        curDynProp = self.GetDynProp(criteria['Column'])
        if curDynProp is None :
            return fullQuery

        if 'date' in curDynProp['TypeProp'].lower() :
            try:
                criteria['Value'] = parse(criteria['Value'].replace(' ',''))
            except: pass

        filterCriteria = eval_.eval_binary_expr(self.GetDynPropValueView().c['Value'+curDynProp['TypeProp']],criteria['Operator'],criteria['Value'] )
        if filterCriteria is not None :
            existQuery = select([self.GetDynPropValueView()])
            existQuery = existQuery.where(
                and_(
                self.GetDynPropValueView().c['Name'] == criteria['Column'],
                filterCriteria
                ))
            existQuery = existQuery.where(self.ObjWithDynProp.ID == self.GetDynPropValueView().c[self.ObjWithDynProp().GetSelfFKNameInValueTable()])
            # fullQuery = fullQuery.where(exists(
            #     existQuery.where(self.ObjWithDynProp.ID == self.GetDynPropValueView().c[self.ObjWithDynProp().GetSelfFKNameInValueTable()])))

        if curDynProp != None and criteria['Operator'].lower() in ['is','is not'] and criteria['Value'].lower() == 'null':
            nullExistQuery = select([self.GetDynPropValueView()])
            nullExistQuery = nullExistQuery.where(
                and_(self.GetDynPropValueView().c['Name'] == criteria['Column']
                    ,self.ObjWithDynProp.ID == self.GetDynPropValueView().c[self.ObjWithDynProp().GetSelfFKNameInValueTable()])
                )
            fullQuery = fullQuery.where(or_(~exists(nullExistQuery),exists(existQuery)))
        else : 
            fullQuery = fullQuery.where(exists(existQuery)) 
        return fullQuery

    def has_property(self, prop):
        property_granted_select = select(
            [null()],
            from_obj=[
                Property.__table__,
                PropertyGroup.__table__,
                Membership.__table__
            ]
        ).where(
            and_(
                Property.name == prop,
                Property.property_group_id == PropertyGroup.id,
                PropertyGroup.id == Membership.group_id,
                Membership.user_id == self.id,
                Membership.active
            )
        )
        #.cte("property_granted_select")
        return and_(
            not_(exists(
                property_granted_select.where(
                    Property.granted == false())

            )),
            exists(
                property_granted_select.where(
                    Property.granted == true()
                )
            )
        )

 def getNumberFromAlias(self, alias):
     if session.query(exists().where(Recipients.alias == alias)).scalar():
         return session.query(Recipients).filter(Recipients.alias == alias).first().phoneNumber
     elif session.query(exists().where(Recipients.phoneNumber == alias)).scalar():
         return alias
     else:
         raise "Unknown alias in getNumberFromAlias"

    def whereInEquipement(self,fullQueryJoin,criteria):
        sensorObj = list(filter(lambda x:'FK_Sensor'==x['Column'], criteria))[0]
        sensor = sensorObj['Value']

        table = Base.metadata.tables['IndividualEquipment']
        joinTable = outerjoin(table,Sensor, table.c['FK_Sensor'] == Sensor.ID)
        startDate = datetime.now()

        if self.startDate :
            startDate = self.startDate

        subSelect= select([table.c['FK_Individual']]
            ).select_from(joinTable).where(Individual.ID== table.c['FK_Individual']).where(table.c['StartDate'] <= startDate)

        if sensorObj['Operator'].lower() in ['is null','is not null'] :
            if not self.history :
                subSelect = subSelect.where(or_(table.c['EndDate'] >= startDate,table.c['EndDate'] == None))

        else:
            subSelect = subSelect.where(eval_.eval_binary_expr(Sensor.UnicIdentifier,sensorObj['Operator'],sensor))
            if not self.history : 
                subSelect = subSelect.where(or_(table.c['EndDate'] >= startDate,table.c['EndDate'] == None))

        if  'is not' in sensorObj['Operator'].lower():
            if sensorObj['Operator'].lower() == 'is not null' :
                fullQueryJoin = fullQueryJoin.where(exists(subSelect))
            else :
                fullQueryJoin = fullQueryJoin.where(~exists(subSelect))
        else :
            if sensorObj['Operator'].lower() == 'is null' :
                fullQueryJoin = fullQueryJoin.where(~exists(subSelect))
            else:
                fullQueryJoin = fullQueryJoin.where(exists(subSelect))
        return fullQueryJoin

 def post(self):
     if not self.current_user:
         return self.send_error(403)
     self.pushid = core.util.get_int_arg(self.request, "id")
     push_query = (
         db.push_pushes.update()
         .where(db.push_pushes.c.id == self.pushid)
         .values({"state": "live", "modified": time.time()})
     )
     request_query = (
         db.push_requests.update()
         .where(
             SA.and_(
                 db.push_requests.c.state == "blessed",
                 SA.exists(
                     [1],
                     SA.and_(
                         db.push_pushcontents.c.push == self.pushid,
                         db.push_pushcontents.c.request == db.push_requests.c.id,
                     ),
                 ),
             )
         )
         .values({"state": "live", "modified": time.time()})
     )
     reset_query = (
         db.push_requests.update()
         .where(
             SA.exists(
                 [1],
                 SA.and_(
                     db.push_requests.c.state == "pickme",
                     db.push_pushcontents.c.push == self.pushid,
                     db.push_pushcontents.c.request == db.push_requests.c.id,
                 ),
             )
         )
         .values({"state": "requested"})
     )
     delete_query = db.push_pushcontents.delete().where(
         SA.exists(
             [1],
             SA.and_(
                 db.push_pushcontents.c.push == self.pushid,
                 db.push_pushcontents.c.request == db.push_requests.c.id,
                 db.push_requests.c.state == "requested",
             ),
         )
     )
     live_query = db.push_requests.select().where(
         SA.and_(
             db.push_requests.c.state == "live",
             db.push_pushcontents.c.push == self.pushid,
             db.push_pushcontents.c.request == db.push_requests.c.id,
         )
     )
     db.execute_transaction_cb(
         [push_query, request_query, reset_query, delete_query, live_query], self.on_db_complete
     )

 def expr(self, i, alias, col, key_groups, t, v, op, last_is_array):
     if i < len(key_groups) - 1:
         subq, c = self.get_subquery(alias, i, key_groups)
         subq = subq.where(self.expr(i + 1, 'dart_a_%s.value' % i, c, key_groups, t, v, op, last_is_array))
         return exists(subq)
     if last_is_array:
         subq, c = self.get_subquery(alias, i, key_groups, True)
         subq = subq.where(op.evaluate(lambda x: x, c, _python_cast(t), v))
         return exists(subq)
     return op.evaluate(_pg_cast(t), col[key_groups[i]], _python_cast(t), v)

    def countQuery(self, criteria=None):
        query = super().countQuery(criteria)

        curEquipmentTable = Base.metadata.tables['CurrentlySensorEquiped']
        MonitoredSiteTable = Base.metadata.tables['MonitoredSite']
        joinTable = outerjoin(
            curEquipmentTable,
            MonitoredSite,
            MonitoredSiteTable.c['ID'] == curEquipmentTable.c['FK_MonitoredSite'])

        for obj in criteria:
            if 'available' in obj['Column']:
                query = self.WhereInJoinTable(query, obj)

            if (obj['Column'] in ['FK_MonitoredSiteName', 'FK_Individual']
                    and obj['Operator'] not in ['is null', 'is not null']):

                queryExist = select(curEquipmentTable.c
                                    ).select_from(joinTable
                                                  ).where(Sensor.ID == curEquipmentTable.c['FK_Sensor'])

                if obj['Column'] == 'FK_MonitoredSiteName':
                    queryExist = queryExist.where(eval_.eval_binary_expr(
                        MonitoredSiteTable.c['Name'],
                        obj['Operator'],
                        obj['Value']))

                if obj['Column'] == 'FK_Individual':
                    queryExist = queryExist.where(eval_.eval_binary_expr(
                        curEquipmentTable.c['FK_Individual'],
                        obj['Operator'],
                        obj['Value']))
                query = query.where(exists(queryExist))

            if (obj['Column'] in ['FK_MonitoredSiteName', 'FK_Individual']
                    and obj['Operator'] in ['is null', 'is not null']):
                queryExist = select(curEquipmentTable.c
                                    ).select_from(joinTable
                                                  ).where(Sensor.ID == curEquipmentTable.c['FK_Sensor'])

                if obj['Column'] == 'FK_Individual':
                    queryExist = queryExist.where(
                        and_(Sensor.ID == curEquipmentTable.c['FK_Sensor'],
                             curEquipmentTable.c['FK_Individual'] != None))

                if obj['Column'] == 'FK_MonitoredSiteName':
                    queryExist = queryExist.where(
                        and_(Sensor.ID == curEquipmentTable.c['FK_Sensor'],
                             curEquipmentTable.c['FK_MonitoredSite'] != None))

                if 'not' in obj['Operator']:
                    query = query.where(exists(queryExist))
                else:
                    query = query.where(not_(exists(queryExist)))
        return query

def create():
    if not session.query(exists(select([("schema_name")]).select_from("information_schema.schemata")\
                                        .where("schema_name = 'surveys'"))).scalar():
        engine.execute(CreateSchema('surveys'))

    if not session.query(exists(select([("schema_name")]).select_from("information_schema.schemata")\
                                        .where("schema_name = 'raw'"))).scalar():
        engine.execute(CreateSchema('raw'))


    Base.metadata.create_all(engine)

 def total_wage_before(cls, last_date):
     Assoc = PersonStandardAssoc
     expression = select(
         [func.sum(Standard.money).label('money')]).where(
             exists().where(and_(
                 Assoc.standard_id == Standard.id,
                 exists().where(and_(
                     Assoc.person_id == cls.id,
                     Assoc.effective_before(last_date)))))).c.money +\
         cls.personal_wage
     return expression.label('total_wage_before')

    def post(self):
        """Creates a new user"""
        # Parse the arguments.
        parser = reqparse.RequestParser()
        parser.add_argument('login', type=str, location='json', required=True, help='Missing login')
        parser.add_argument('password_hash', type=str, location='json', required=True, help='Missing password')
        parser.add_argument('role', type=str, location='json', required=True, help='Missing role')
        parser.add_argument('entity_id', type=str, location='json', required=True, help='Missing entity_id.')
        parser.add_argument('last_name', type=str, location='json')
        parser.add_argument('first_name', type=str, location='json')
        args = parser.parse_args()

        first_name = args.get('first_name', None)
        last_name = args.get('last_name', None)
        role = args['role']
        entity_id = args['entity_id']
        login = args['login']
        password_hash = args['password_hash']

        ensure_allowed('create', 'user')

        # Check if the role is correct
        if role not in roles:
            app.logger.warning('Request on POST UserListAPI for non existing or missing role')
            return {'error': 'Role POSTed is not allowed'}, 412

        # We check if another non deleted user has the same login
        user_same_login_exists = request.dbs.query(exists().where(and_(User.login == login,
                                                                       not_(User.deleted)))).scalar()
        if user_same_login_exists:
            return {'error': 'User with the same login exists.'}, 412

        # Check if the entity exists
        if not request.dbs.query(exists().where(and_(model.Entity.id == entity_id,
                                                     not_(model.Entity.deleted)))).scalar():
            app.logger.warning('Request on POST UserListAPI with entity_id {} not found'.format(entity_id))
            return {'error': 'entity_id doesn\'t exists'}, 412

        # Write the user in DB.
        user_id = generate_uuid_for(request.dbs, User)
        u = User(
            id=user_id,
            login=login,
            password_hash=hashlib.sha1(password_hash).hexdigest(),
            last_name=last_name,
            first_name=first_name,
            role=role,
            deleted=False,
            entity_id=entity_id
        )
        request.dbs.add(u)
        app.logger.info('User {} (uuid: {}) created'.format(login, user_id))
        return marshal(u.to_dict(), user_fields), 201

    def countFilterOnDynProp(self, fullQuery, criteria):
        curDynProp = self.GetDynProp(criteria['Column'])
        countHisto = False

        if curDynProp is None:
            return fullQuery

        if self.history:
            countHisto = True
        if 'date' in curDynProp['TypeProp'].lower():
            try:
                criteria['Value'] = parse(criteria['Value'].replace(' ', ''))
            except:
                pass

        filterCriteria = eval_.eval_binary_expr(
            self.GetDynPropValueView(
                countHisto=countHisto).c['Value' + curDynProp['TypeProp']],
                criteria['Operator'],
                criteria['Value'])
        if filterCriteria is not None and 'null' not in criteria['Operator'].lower():
            existQuery = select(
                [self.GetDynPropValueView(countHisto=countHisto)])
            existQuery = existQuery.where(
                and_(
                    self.GetDynPropValueView(countHisto=countHisto).c[
                        'Name'] == criteria['Column'],
                    filterCriteria
                ))
            existQuery = existQuery.where(self.ObjWithDynProp.ID == self.GetDynPropValueView(
                countHisto=countHisto).c[self.ObjWithDynProp().GetSelfFKNameInValueTable()])
            fullQuery = fullQuery.where(exists(existQuery))

        elif 'null' in criteria['Operator'].lower():
            existQuery = select(
                [self.GetDynPropValueView(countHisto=countHisto)])
            existQuery = existQuery.where(
                self.GetDynPropValueView(countHisto=countHisto).c[
                    'Name'] == criteria['Column'],
            )
            existQuery = existQuery.where(self.ObjWithDynProp.ID == self.GetDynPropValueView(
                countHisto=countHisto).c[self.ObjWithDynProp().GetSelfFKNameInValueTable()])

            if 'is null' == criteria['Operator'].lower():
                fullQuery = fullQuery.where(or_(
                    exists(existQuery.where(filterCriteria)),
                    ~exists(existQuery)
                ))
            else:
                fullQuery = fullQuery.where(
                    exists(existQuery.where(filterCriteria)))
        return fullQuery