def getCountForCashConversionChart(self): session = loadSession() count = session.query( PaymVoucFroz.c.revision, PaymVoucFroz.c.charged_by_time, PaymVoucFroz.c.announced_by_time, PaymVoucFroz.c.payed_by_time, PaymVoucFroz.c.announced_by_doc_time, PaymVoucFroz.c.payed_by_doc_time, PaymVoucFroz.c.booked_charged_by_time, PaymVoucFroz.c.booked_payed_by_time, PaymVoucFroz.c.charged_by_netto_sums, PaymVoucFroz.c.frozen_val, PaymVouc.c.belegdatum, PaymVouc.c.belegid, PaymVouc.c.valid_until, PaymVouc.c.primanotaid, PaymVouc.c.vunr, PaymVouc.c.suppliersettled_at, PaymVouc.c.charged_by, ShopData.c.vunr, ShopData.c.objectname)\ .filter(PaymVouc.c.belegid == PaymVoucFroz.c.belegid, PaymVoucFroz.c.revision == 0, ShopData.c.vunr == PaymVouc.c.vunr, and_( func.to_date(PaymVouc.c.belegdatum, 'YYMMDD') >= self.__st_date, func.to_date(PaymVouc.c.belegdatum, 'YYMMDD') <= self.__end_date), PaymVouc.c.corporateno == self.__corporateId if self.__corporateId else '', PaymVouc.c.vunr == self.__supplierId if self.__supplierId else '')\ .distinct()\ .count() session.close() return count
def getDataForDeadLine(self): session = loadSession() select = session.query(PaymVouc.c.corporateno, PaymVouc.c.belegdatum, PaymVouc.c.belegid, PaymVouc.c.vunr, PaymVouc.c.valid_until, PaymVouc.c.suppliersettled_at, PaymVoucFroz.c.revision, PaymVoucFroz.c.payed_by_time, PaymVoucFroz.c.payed_by_doc_time, PaymVoucFroz.c.frozen_val, PaymVoucFroz.c.announced_by_doc_time, PaymVoucFroz.c.booked_payed_by_time, PaymVoucFroz.c.booked_charged_by_time, PaymVoucFroz.c.charged_by_doc, ShopData.c.vunr, ShopData.c.objectname)\ .filter(PaymVouc.c.belegid == PaymVoucFroz.c.belegid, PaymVoucFroz.c.revision == 0, ShopData.c.vunr == PaymVouc.c.vunr, and_( func.to_date(PaymVouc.c.belegdatum, 'YYMMDD') >= self.__st_date, func.to_date(PaymVouc.c.belegdatum, 'YYMMDD') <= self.__end_date), PaymVouc.c.corporateno == self.__corporateId if self.__corporateId else '', PaymVouc.c.vunr == self.__supplierId if self.__supplierId else '')\ .distinct() session.close() return select.all()
def check_value_type(value): if isinstance(value, datetime.datetime): return func.to_date(value, "yyyy-mm-dd hh24:mi:ss") elif isinstance(value, datetime.date): return func.to_date(value, "yyyy-mm-dd") else: return value
def get_ttd_restitusi_kompensasi(cls, kantor): sekarang = datetime.now().strftime("%Y-%m-%d") return Pegawai.query().\ filter(cls.nip == Pegawai.nip).\ filter(cls.kd_kanwil == kantor['kd_kanwil']).\ filter(cls.kd_kantor == kantor['kd_kantor']).\ filter(cls.kd_jabatan == '10', cls.kd_wewenang == '10').\ filter(cls.tgl_awal_berlaku <= func.to_date(sekarang, 'YYYY-MM-DD'), cls.tgl_akhir_berlaku >= func.to_date(sekarang, 'YYYY-MM-DD')).\ first() #END OF SCRIPT
def AddNewClass(batchcode,spycode,segmentcode,learningcentercode): i=1 for tcprow in session.query(tcp).filter_by(batchcode=batchcode).\ filter_by(spycode = spycode): scode = batch[2:4]+str(learningcentercode)+tcprow.studenttype+tcprow.professionallevel retclass= session.query(classinfo).filter_by(spycode=tcprow.spycode).\ filter_by(studentcategory=str(tcprow.studenttype)+str(tcprow.professionallevel)).\ filter_by(learningcentercode=learningcentercode).\ filter_by(batchcode=batchcode).first() classname='分部'+str(segmentcode)+':'+batch[2:6] if retclass is None: newclasscode = scode+getMaxClass(scode) print('add new class %s'%(newclasscode,)) retspy = session.query(spyinfo).filter_by(spycode=spycode).first() if retspy is not None: classname=classname+retspy.spyname seq=Sequence('org_class_seq') nextid = engine.execute(seq) dt = func.to_date(datetime.datetime.now,'yyyy-mm-dd H:M:S') #print(nextid) newclass = classinfo(classid=nextid,batchcode=batchcode,learningcentercode=learningcentercode,classcode=newclasscode,classname=classname,studentcategory=str(tcprow.studenttype)+str(tcprow.professionallevel),spycode=spycode,professionallevel=tcprow.professionallevel,examsitecode=learningcentercode,classteacher='') session.add(newclass) #session.flush() session.commit() else : classname=retclass.classname print('classname : %s ' % (classname,))
def AddNewClass(batchcode,spycode,segmentcode,learningcentercode): i=1 for tcprow in session.query(tcp).filter_by(batchcode=batchcode).\ filter_by(spycode = spycode): scode = batch[2:4]+str(learningcentercode)+tcprow.studenttype+tcprow.professionallevel retclass= session.query(classinfo).filter_by(spycode=tcprow.spycode).\ filter_by(studentcategory=str(tcprow.studenttype)+str(tcprow.professionallevel)).\ filter_by(learningcentercode=learningcentercode).\ filter_by(batchcode=batchcode).first() classname='分部'+str(segmentcode)+':'+batch[2:6] if retclass is None: newclasscode = scode+getMaxClass(scode) print('add new class %s'%(newclasscode,)) retspy = session.query(spyinfo).filter_by(spycode=spycode).first() if retspy is not None: classname=classname+retspy.spyname seq=Sequence('org_class_seq') nextid = engine.execute(seq) dt = func.to_date(datetime.datetime.now,'yyyy-mm-dd H:M:S') dt2 ='20'+ time.strftime('%y/%m/%d',time.localtime()) #print(nextid) newclass = classinfo(classid=nextid,batchcode=batchcode,learningcentercode=learningcentercode,classcode=newclasscode,classname=classname,studentcategory=str(tcprow.studenttype)+str(tcprow.professionallevel),spycode=spycode,professionallevel=tcprow.professionallevel,examsitecode=learningcentercode,classteacher='',createtime=func.now()) session.add(newclass) #session.flush() session.commit() else : classname=retclass.classname print('classname : %s ' % (classname,))
def get_suppliers_with_expiring_documents(self, days): today = datetime.now(pytz.timezone('Australia/Sydney')) # Find out which of the supplier's documents have expired or are expiring soon liability = (select([Supplier.code, Supplier.name, literal('liability').label('type'), Supplier.data['documents']['liability']['expiry'].astext.label('expiry')]) .where(and_(Supplier.data['documents']['liability']['expiry'].isnot(None), func.to_date(Supplier.data['documents']['liability']['expiry'].astext, 'YYYY-MM-DD') == (today.date() + timedelta(days=days))))) workers = (select([Supplier.code, Supplier.name, literal('workers').label('type'), Supplier.data['documents']['workers']['expiry'].astext.label('expiry')]) .where(and_(Supplier.data['documents']['workers']['expiry'].isnot(None), func.to_date(Supplier.data['documents']['workers']['expiry'].astext, 'YYYY-MM-DD') == (today.date() + timedelta(days=days))))) expiry_dates = union(liability, workers).alias('expiry_dates') # Aggregate the document details so they can be returned with the results documents = (db.session.query(expiry_dates.columns.code, expiry_dates.columns.name, func.json_agg( func.json_build_object( 'type', expiry_dates.columns.type, 'expiry', expiry_dates.columns.expiry)).label('documents')) .group_by(expiry_dates.columns.code, expiry_dates.columns.name) .subquery('expired_documents')) # Find email addresses associated with the supplier email_addresses = self.get_supplier_contacts_union() # Aggregate the email addresses so they can be returned with the results aggregated_emails = (db.session.query(email_addresses.columns.code, func.json_agg( email_addresses.columns.email_address ).label('email_addresses')) .group_by(email_addresses.columns.code) .subquery()) # Combine the list of email addresses and documents results = (db.session.query(documents.columns.code, documents.columns.name, documents.columns.documents, aggregated_emails.columns.email_addresses) .join(aggregated_emails, documents.columns.code == aggregated_emails.columns.code) .order_by(documents.columns.code) .all()) return [r._asdict() for r in results]
def filter_query_all_filters(model, q, filters, user): """ Return a query filtered with the cruved and all the filters available in the synthese form parameters: - q (SQLAchemyQuery): an SQLAchemy query - filters (dict): a dict of filter - user (User): a user object from User - allowed datasets (List<int>): an array of ID dataset where the users have autorization """ q = filter_query_with_cruved(model, q, user) if "observers" in filters: q = q.filter(model.observers.ilike("%" + filters.pop("observers")[0] + "%")) if "date_min" in filters: q = q.filter(model.date_min >= filters.pop("date_min")[0]) if "date_max" in filters: q = q.filter(model.date_min <= filters.pop("date_max")[0]) if "id_acquisition_frameworks" in filters: q = q.join( TAcquisitionFramework, model.id_acquisition_framework == TAcquisitionFramework.id_acquisition_framework, ) q = q.filter( TAcquisitionFramework.id_acquisition_framework.in_( filters.pop("id_acquisition_frameworks") ) ) if "geoIntersection" in filters: # Insersect with the geom send from the map ors = [] for str_wkt in filters["geoIntersection"]: # if the geom is a circle if "radius" in filters: radius = filters.pop("radius")[0] wkt = circle_from_point(wkt, float(radius)) else: wkt = loads(str_wkt) geom_wkb = from_shape(wkt, srid=4326) ors.append(model.the_geom_4326.ST_Intersects(geom_wkb)) q = q.filter(or_(*ors)) filters.pop("geoIntersection") if "period_start" in filters and "period_end" in filters: period_start = filters.pop("period_start")[0] period_end = filters.pop("period_end")[0] q = q.filter( or_( func.gn_commons.is_in_period( func.date(model.date_min), func.to_date(period_start, "DD-MM"), func.to_date(period_end, "DD-MM"), ), func.gn_commons.is_in_period( func.date(model.date_max), func.to_date(period_start, "DD-MM"), func.to_date(period_end, "DD-MM"), ), ) ) q, filters = filter_taxonomy(model, q, filters) # generic filters join_on_cor_area = False for colname, value in filters.items(): if colname.startswith("area"): if not join_on_cor_area: q = q.join( CorAreaSynthese, CorAreaSynthese.id_synthese == model.id_synthese ) q = q.filter(CorAreaSynthese.id_area.in_(value)) join_on_cor_area = True elif colname.startswith("modif_since_validation"): q = q.filter(model.meta_update_date > model.validation_date) else: col = getattr(model.__table__.columns, colname) q = q.filter(col.in_(value)) return q
def filter_query_all_filters(model, q, filters, user): """ Return a query filtered with the cruved and all the filters available in the synthese form parameters: - q (SQLAchemyQuery): an SQLAchemy query - filters (dict): a dict of filter - user (User): a user object from User - allowed datasets (List<int>): an array of ID dataset where the users have autorization """ q = filter_query_with_cruved(model, q, user) if "observers" in filters: q = q.filter( model.observers.ilike("%" + filters.pop("observers")[0] + "%")) if "id_organism" in filters: id_datasets = (DB.session.query(CorDatasetActor.id_dataset).filter( CorDatasetActor.id_organism.in_(filters.pop("id_organism"))).all()) formated_datasets = [d[0] for d in id_datasets] q = q.filter(model.id_dataset.in_(formated_datasets)) if "date_min" in filters: q = q.filter(model.date_min >= filters.pop("date_min")[0]) if "date_max" in filters: # set the date_max at 23h59 because a hour can be set in timestamp date_max = datetime.datetime.strptime( filters.pop("date_max")[0], '%Y-%m-%d') date_max = date_max.replace(hour=23, minute=59, second=59) q = q.filter(model.date_max <= date_max) if "id_acquisition_framework" in filters: q = q.join( TAcquisitionFramework, model.id_acquisition_framework == TAcquisitionFramework.id_acquisition_framework, ) q = q.filter( TAcquisitionFramework.id_acquisition_framework.in_( filters.pop("id_acquisition_framework"))) if "geoIntersection" in filters: # Intersect with the geom send from the map ors = [] for str_wkt in filters["geoIntersection"]: # if the geom is a circle if "radius" in filters: radius = filters.pop("radius")[0] wkt = loads(str_wkt) wkt = circle_from_point(wkt, float(radius)) else: wkt = loads(str_wkt) geom_wkb = from_shape(wkt, srid=4326) ors.append(model.the_geom_4326.ST_Intersects(geom_wkb)) q = q.filter(or_(*ors)) filters.pop("geoIntersection") if "period_start" in filters and "period_end" in filters: period_start = filters.pop("period_start")[0] period_end = filters.pop("period_end")[0] q = q.filter( or_( func.gn_commons.is_in_period( func.date(model.date_min), func.to_date(period_start, "DD-MM"), func.to_date(period_end, "DD-MM"), ), func.gn_commons.is_in_period( func.date(model.date_max), func.to_date(period_start, "DD-MM"), func.to_date(period_end, "DD-MM"), ), )) q, filters = filter_taxonomy(model, q, filters) # generic filters join_on_cor_area = False for colname, value in filters.items(): if colname.startswith("area"): if not join_on_cor_area: q = q.join(CorAreaSynthese, CorAreaSynthese.id_synthese == model.id_synthese) q = q.filter(CorAreaSynthese.id_area.in_(value)) join_on_cor_area = True else: col = getattr(model.__table__.columns, colname) q = q.filter(col.in_(value)) return q
def filter_other_filters(self): """ Other filters """ if "id_dataset" in self.filters: self.query = self.query.where( self.model.id_dataset.in_(self.filters.pop("id_dataset"))) if "observers" in self.filters: #découpe des éléments saisies par les espaces observers = (self.filters.pop("observers")[0]).split() self.query = self.query.where( and_(*[ self.model.observers.ilike("%" + observer + "%") for observer in observers ])) if "observers_list" in self.filters: self.query = self.query.where( and_(*[ self.model.observers.ilike("%" + observer.get('nom_complet') + "%") for observer in self.filters.pop("observers_list") ])) if "id_organism" in self.filters: datasets = (DB.session.query(CorDatasetActor.id_dataset).filter( CorDatasetActor.id_organism.in_( self.filters.pop("id_organism"))).all()) formated_datasets = [d[0] for d in datasets] self.query = self.query.where( self.model.id_dataset.in_(formated_datasets)) if "date_min" in self.filters: self.query = self.query.where( self.model.date_min >= self.filters.pop("date_min")[0]) if "date_max" in self.filters: # set the date_max at 23h59 because a hour can be set in timestamp date_max = datetime.datetime.strptime( self.filters.pop("date_max")[0], '%Y-%m-%d') date_max = date_max.replace(hour=23, minute=59, second=59) self.query = self.query.where(self.model.date_max <= date_max) if "id_acquisition_framework" in self.filters: self.query = self.query.where( self.model.id_acquisition_framework.in_( self.filters.pop("id_acquisition_framework"))) if "geoIntersection" in self.filters: # Insersect with the geom send from the map ors = [] for str_wkt in self.filters["geoIntersection"]: # if the geom is a circle if "radius" in self.filters: radius = self.filters.pop("radius")[0] wkt = loads(str_wkt) wkt = circle_from_point(wkt, float(radius)) else: wkt = loads(str_wkt) geom_wkb = from_shape(wkt, srid=4326) ors.append(self.model.the_geom_4326.ST_Intersects(geom_wkb)) self.query = self.query.where(or_(*ors)) self.filters.pop("geoIntersection") if "period_start" in self.filters and "period_end" in self.filters: period_start = self.filters.pop("period_start")[0] period_end = self.filters.pop("period_end")[0] self.query = self.query.where( or_( func.gn_commons.is_in_period( func.date(self.model.date_min), func.to_date(period_start, "DD-MM"), func.to_date(period_end, "DD-MM"), ), func.gn_commons.is_in_period( func.date(self.model.date_max), func.to_date(period_start, "DD-MM"), func.to_date(period_end, "DD-MM"), ), )) # use for validation module since the class is factorized if "modif_since_validation" in self.filters: self.query = self.query.where( self.model.meta_update_date > self.model.validation_date) self.filters.pop("modif_since_validation") # generic filters for colname, value in self.filters.items(): if colname.startswith("area"): self.add_join(CorAreaSynthese, CorAreaSynthese.id_synthese, self.model.id_synthese) self.query = self.query.where( CorAreaSynthese.id_area.in_(value)) elif colname.startswith("id_"): col = getattr(self.model.__table__.columns, colname) self.query = self.query.where(col.in_(value)) else: col = getattr(self.model.__table__.columns, colname) self.query = self.query.where( col.ilike("%{}%".format(value[0])))
def filter_query_all_filters(model, q, filters, user): """ Return a query filtered with the cruved and all the filters available in the synthese form parameters: - q (SQLAchemyQuery): an SQLAchemy query - filters (dict): a dict of filter - user (User): a user object from User - allowed datasets (List<int>): an array of ID dataset where the users have autorization """ q = filter_query_with_cruved(model, q, user) if "observers" in filters: q = q.filter(model.observers.ilike("%" + filters.pop("observers")[0] + "%")) if "id_organism" in filters: id_datasets = ( DB.session.query(CorDatasetActor.id_dataset) .filter(CorDatasetActor.id_organism.in_(filters.pop("id_organism"))) .all() ) formated_datasets = [d[0] for d in id_datasets] q = q.filter(model.id_dataset.in_(formated_datasets)) if "date_min" in filters: q = q.filter(model.date_min >= filters.pop("date_min")[0]) if "date_max" in filters: q = q.filter(model.date_min <= filters.pop("date_max")[0]) if "id_acquisition_framework" in filters: q = q.join( TAcquisitionFramework, model.id_acquisition_framework == TAcquisitionFramework.id_acquisition_framework, ) q = q.filter( TAcquisitionFramework.id_acquisition_framework.in_( filters.pop("id_acquisition_frameworks") ) ) if "geoIntersection" in filters: # Insersect with the geom send from the map ors = [] for str_wkt in filters["geoIntersection"]: # if the geom is a circle if "radius" in filters: radius = filters.pop("radius")[0] wkt = loads(str_wkt) wkt = circle_from_point(wkt, float(radius)) else: wkt = loads(str_wkt) geom_wkb = from_shape(wkt, srid=4326) ors.append(model.the_geom_4326.ST_Intersects(geom_wkb)) q = q.filter(or_(*ors)) filters.pop("geoIntersection") if "period_start" in filters and "period_end" in filters: period_start = filters.pop("period_start")[0] period_end = filters.pop("period_end")[0] q = q.filter( or_( func.gn_commons.is_in_period( func.date(model.date_min), func.to_date(period_start, "DD-MM"), func.to_date(period_end, "DD-MM"), ), func.gn_commons.is_in_period( func.date(model.date_max), func.to_date(period_start, "DD-MM"), func.to_date(period_end, "DD-MM"), ), ) ) q, filters = filter_taxonomy(model, q, filters) # generic filters join_on_cor_area = False for colname, value in filters.items(): if colname.startswith("area"): if not join_on_cor_area: q = q.join( CorAreaSynthese, CorAreaSynthese.id_synthese == model.id_synthese ) q = q.filter(CorAreaSynthese.id_area.in_(value)) join_on_cor_area = True else: col = getattr(model.__table__.columns, colname) q = q.filter(col.in_(value)) return q
def latest_query(db, var_id, identifier_id, start_date, end_date, location, allowed_location=1, level=None, weeks=False, date_variable=None, week_offset=0): """ To query register like data where we want to get the latest value. I.e If the value of the number of beds is updated each week and we want the latest number. We take the latest value per clinic. Args: var_id: Variable id to get last of identifier_id: Id to identify which records we should use start_date: Start date end_date: End date location: Location to restrict to date_variable: if None we use date from data otherwise we use the variable indicated weeks: True if we want a breakdwon by weeks. Returns: result(dict): Dictionary with results. Always has total key, and if level was given there is a level key with the data breakdown """ if allowed_location == 1: if g: allowed_location = g.allowed_location if not is_allowed_location(location, allowed_location): return {} location_condtion = [ or_(loc == location for loc in (Data.country, Data.zone, Data.region, Data.district, Data.clinic)) ] if date_variable: date_conditions = [ func.to_date(Data.variables[date_variable].astext, "YYYY-MM-DDTHH-MI-SS") >= start_date, func.to_date(Data.variables[date_variable].astext, "YYYY-MM-DDTHH-MI-SS") < end_date ] else: date_conditions = [Data.date >= start_date, Data.date < end_date] conditions = location_condtion + date_conditions + [ Data.variables.has_key(identifier_id) ] if weeks: epi_year_start = meerkat_abacus.util.epi_week.epi_year_start_date( start_date) if date_variable: c = func.floor( extract( 'days', func.to_date(Data.variables[date_variable].astext, "YYYY-MM-DDTHH-MI-SS") - epi_year_start) / 7 + 1).label("week") else: c = func.floor( extract('days', Data.date - epi_year_start) / 7 + 1).label("week") # This query selects that latest record for each clinic for each week # that has the variable identifier_id query = db.session.query( Data.clinic, c, Data.date, Data.region, Data.district, Data.variables).distinct( Data.clinic, c).filter(*conditions).order_by( Data.clinic).order_by(c).order_by(Data.date.desc()) ret = { "total": 0, "weeks": {}, "district": {}, "clinic": {}, "region": {} } for r in query: val = r.variables.get(var_id, 0) ret["total"] += val week = int(r.week) - week_offset ret["weeks"].setdefault(week, 0) ret["weeks"][week] += val ret["clinic"].setdefault(r.clinic, {"total": 0, "weeks": {}}) ret["clinic"][r.clinic]["total"] += val ret["clinic"][r.clinic]["weeks"][week] = val ret["district"].setdefault(r.district, {"total": 0, "weeks": {}}) ret["district"][r.district]["total"] += val ret["district"][r.district]["weeks"][week] = +val ret["region"].setdefault(r.region, {"total": 0, "weeks": {}}) ret["region"][r.region]["total"] += val ret["region"][r.region]["weeks"][week] = +val return ret else: # This query selects that latest record for each clinic # that has the variable identifier_id query = db.session.query( Data.clinic, Data.date, Data.region, Data.district, Data.variables).distinct(Data.clinic).filter(*conditions).order_by( Data.clinic).order_by(Data.date.desc()) ret = {"total": 0, "clinic": {}, "district": {}, "region": {}} for r in query: val = r.variables.get(var_id, 0) ret["total"] += val ret["clinic"][r.clinic] = val ret["district"].setdefault(r.district, 0) ret["district"][r.district] += val ret["region"].setdefault(r.region, 0) ret["region"][r.region] += val return ret
def populateHourlyUtil(): try: # Getting current date and current hour currDT = str(dt.now()) currDate = currDT[0:currDT.find(' ')] currHour = currDT[currDT.find(' '):currDT.find(':')].strip() currHourInt = int(currHour) # Getting capacity of the current parking lot activePL = ParkingLot.query.filter_by(pl_active='t').first() parkingLotCapacity = int(activePL.pl_capacity) # Getting number of current occupied slots and utilization percentage currActiveTokens = Token.query.filter_by(exit_date=None).all() currUtil = parkingLotCapacity - len(currActiveTokens) currUtilPercent = (currUtil * 100) / parkingLotCapacity if (currHourInt == 0): prevDT = str(dt.now() - timedelta(days=1)) prevDate = prevDT[0:prevDT.find(' ')] # Getting exit transactions of the last hour of the previous day lastHourTransactions = Token.query.filter( between( Token.exit_date, func.to_date(prevDate + " 23:00", "YYYY-MM-DD HH24:MI"), func.to_date(prevDate + " 23:59", "YYYY-MM-DD HH24:MI"))).all() # Getting revenue collected till start of the last hour prevRev = 0.0 lastlastHourUtil = HourlyUtil.query.filter( and_( HourlyUtil.util_date == func.to_date( prevDate, "YYYY-MM-DD"), HourlyUtil.util_hour == 22)).first() if (lastlastHourUtil is None): prevRev = 0.0 else: prevRev = float(lastlastHourUtil.rev) # Getting revenue collected in the last hour currRev = 0.0 for lastHourTransaction in lastHourTransactions: currRev = currRev + float(lastHourTransaction.computed_charge) # Computing cumulative revenue and storing as a new row in the Hourly Util table currCumuRev = currRev + prevRev hourlyUtilEntry = HourlyUtil(prevDate, 23, currUtilPercent, currCumuRev) db.session.add(hourlyUtilEntry) db.session.commit() else: # Getting exit transactions of the last hour lastHourTransactions = Token.query.filter( between( Token.exit_date, func.to_date(currDate + " " + str(currHourInt - 1) + ":00", "YYYY-MM-DD HH24:MI"), func.to_date(currDate + " " + str(currHourInt - 1) + ":59", "YYYY-MM-DD HH24:MI"))).all() # Getting revenue collected till start of the last hour prevRev = 0.0 if (currHourInt >= 2): lastlastHourUtil = HourlyUtil.query.filter( and_( HourlyUtil.util_date == func.to_date( currDate, "YYYY-MM-DD"), HourlyUtil.util_hour == (currHourInt - 2))).first() if (lastlastHourUtil is None): prevRev = 0.0 else: prevRev = float(lastlastHourUtil.rev) # Getting revenue collected in the last hour currRev = 0.0 for lastHourTransaction in lastHourTransactions: currRev = currRev + float(lastHourTransaction.computed_charge) # Computing cumulative revenue and storing as a new row in the Hourly Util table currCumuRev = currRev + prevRev hourlyUtilEntry = HourlyUtil(currDate, currHour, currUtilPercent, currCumuRev) db.session.add(hourlyUtilEntry) db.session.commit() except Exception, e: print e
def filter_query_all_filters(model, q, filters, user, allowed_datasets): """ Return a query filtered with the cruved and all the filters available in the synthese form parameters: - q (SQLAchemyQuery): an SQLAchemy query - filters (dict): a dict of filter - user (TRoles): a user object from TRoles - allowed datasets (List<int>): an array of ID dataset where the users have autorization """ # from geonature.core.users.models import UserRigth # user = UserRigth( # id_role=user.id_role, # tag_object_code='3', # tag_action_code="R", # id_organisme=user.id_organisme, # nom_role='Administrateur', # prenom_role='test' # ) q = filter_query_with_cruved(model, q, user, allowed_datasets) if 'observers' in filters: q = q.filter( model.observers.ilike('%' + filters.pop('observers')[0] + '%')) if 'date_min' in filters: q = q.filter(model.date_min >= filters.pop('date_min')[0]) if 'date_max' in filters: q = q.filter(model.date_min <= filters.pop('date_max')[0]) if 'id_acquisition_frameworks' in filters: q = q.join( TAcquisitionFramework, model.id_acquisition_framework == TAcquisitionFramework.id_acquisition_framework) q = q.filter( TAcquisitionFramework.id_acquisition_framework.in_( filters.pop('id_acquisition_frameworks'))) if 'municipalities' in filters: q = q.filter( model.id_municipality.in_( [com for com in filters['municipalities']])) filters.pop('municipalities') if 'geoIntersection' in filters: # Insersect with the geom send from the map geom_wkt = loads(request.args['geoIntersection']) # if the geom is a circle if 'radius' in filters: radius = filters.pop('radius')[0] geom_wkt = circle_from_point(geom_wkt, float(radius)) geom_wkb = from_shape(geom_wkt, srid=4326) q = q.filter(model.the_geom_4326.ST_Intersects(geom_wkb)) filters.pop('geoIntersection') if 'period_start' in filters and 'period_end' in filters: period_start = filters.pop('period_start')[0] period_end = filters.pop('period_end')[0] q = q.filter( or_( func.gn_commons.is_in_period( func.date(model.date_min), func.to_date(period_start, 'DD-MM'), func.to_date(period_end, 'DD-MM')), func.gn_commons.is_in_period( func.date(model.date_max), func.to_date(period_start, 'DD-MM'), func.to_date(period_end, 'DD-MM')))) q, filters = filter_taxonomy(model, q, filters) # generic filters join_on_cor_area = False for colname, value in filters.items(): if colname.startswith('area'): if not join_on_cor_area: q = q.join(CorAreaSynthese, CorAreaSynthese.id_synthese == model.id_synthese) q = q.filter(CorAreaSynthese.id_area.in_(value)) join_on_cor_area = True else: col = getattr(model.__table__.columns, colname) q = q.filter(col.in_(value)) return q
def get(self, variable, group_by, start_date=None, end_date=None, only_loc=None, use_ids=None, date_variable=None, additional_variables=None, group_by_variables=None): variable = str(variable) if not only_loc: if "only_loc" in request.args: only_loc = request.args["only_loc"] else: only_loc = g.allowed_location if not is_allowed_location(only_loc, g.allowed_location): return {} start_date, end_date = fix_dates(start_date, end_date) if "use_ids" in request.args.keys() or use_ids: use_ids = True else: use_ids = False if date_variable: date_conditions = [ func.to_date(Data.variables[date_variable].astext, "YYYY-MM-DDTHH-MI-SS") >= start_date, func.to_date(Data.variables[date_variable].astext, "YYYY-MM-DDTHH-MI-SS") < end_date ] else: date_conditions = [Data.date >= start_date, Data.date < end_date] if "location" in variable: location_id = variable.split(":")[1] conditions = date_conditions + [ or_(loc == location_id for loc in (Data.country, Data.zone, Data.region, Data.district, Data.clinic)) ] else: conditions = [Data.variables.has_key(variable)] + date_conditions if additional_variables: # add additional variable filters if there are and for i in additional_variables: conditions.append(Data.variables.has_key(i)) if only_loc: conditions += [ or_(loc == only_loc for loc in (Data.country, Data.zone, Data.region, Data.district, Data.clinic)) ] epi_year_start = meerkat_abacus.util.epi_week.epi_year_start_date( start_date) # Determine which columns we want to extract from the Data table columns_to_extract = [func.count(Data.id).label('value')] if date_variable: columns_to_extract.append( func.floor( extract( 'days', func.to_date(Data.variables[date_variable].astext, "YYYY-MM-DDTHH-MI-SS") - epi_year_start) / 7 + 1).label("week")) else: columns_to_extract.append( func.floor( extract('days', Data.date - epi_year_start) / 7 + 1).label("week")) # We want to add the columns to extract based on the group_by value # in addition we create a names dict that translates ids to names if "locations" in group_by: # If we have locations in group_by we also specify the level at # which we want to group the locations, clinic, district or region if ":" in group_by: level = group_by.split(":")[1] else: level = "clinic" locations = abacus_util.get_locations(db.session) ids = locations.keys() names = get_locations_by_level(level, only_loc) columns_to_extract += [getattr(Data, level, None)] group_by_query = level else: if not group_by_variables: names = get_variables(group_by) else: names = group_by_variables if len(names) == 0: return {} ids = names.keys() for i in ids: columns_to_extract.append( Data.variables.has_key(str(i)).label("id" + str(i))) group_by_query = ",".join(["id" + str(i) for i in ids]) if use_ids: names = {vid: vid for vid in names.keys()} start_epi_week = abacus_util.epi_week.epi_week_for_date(start_date)[1] end_epi_week = abacus_util.epi_week.epi_week_for_date(end_date)[1] # How we deal with start and end dates in different years if start_date.year != end_date.year: end_epi_week += 53 * (end_date.year - start_date.year) # DB Query results = db.session.query(*tuple(columns_to_extract)).filter( *conditions).group_by("week," + group_by_query) # Assemble return dict ret = {} for n in names.values(): ret[n] = { "total": 0, "weeks": {i: 0 for i in range(start_epi_week, end_epi_week + 1)} } for r in results: # r = (number, week, other_columns_to_extract if "locations" in group_by: # r[2] = location if r[2]: ret[names[r[2]]]["total"] += r[0] ret[names[r[2]]]["weeks"][int(r[1])] = int(r[0]) else: # r[2:] are the ids that the record has for i, i_d in enumerate(ids): if r[i + 2]: ret[names[i_d]]["total"] += r[0] ret[names[i_d]]["weeks"][int(r[1])] = int(r[0]) return ret
def filter_other_filters(self): """ Other filters """ if "has_medias" in self.filters: self.query = self.query.where( self.model.has_medias ) if "id_dataset" in self.filters: self.query = self.query.where( self.model.id_dataset.in_(self.filters.pop("id_dataset")) ) if "observers" in self.filters: # découpe des éléments saisies par les espaces observers = (self.filters.pop("observers")[0]).split() self.query = self.query.where( and_(*[self.model.observers.ilike("%" + observer + "%") for observer in observers]) ) if "observers_list" in self.filters: self.query = self.query.where( and_( *[ self.model.observers.ilike("%" + observer.get("nom_complet") + "%") for observer in self.filters.pop("observers_list") ] ) ) if "id_organism" in self.filters: datasets = ( DB.session.query(CorDatasetActor.id_dataset) .filter(CorDatasetActor.id_organism.in_(self.filters.pop("id_organism"))) .all() ) formated_datasets = [d[0] for d in datasets] self.query = self.query.where(self.model.id_dataset.in_(formated_datasets)) if "date_min" in self.filters: self.query = self.query.where(self.model.date_min >= self.filters.pop("date_min")[0]) if "date_max" in self.filters: # set the date_max at 23h59 because a hour can be set in timestamp date_max = datetime.datetime.strptime(self.filters.pop("date_max")[0], "%Y-%m-%d") date_max = date_max.replace(hour=23, minute=59, second=59) self.query = self.query.where(self.model.date_max <= date_max) if "id_acquisition_framework" in self.filters: if hasattr(self.model, 'id_acquisition_framework'): self.query = self.query.where( self.model.id_acquisition_framework.in_( self.filters.pop("id_acquisition_framework") ) ) else: self.add_join(TDatasets, self.model.id_dataset, TDatasets.id_dataset) self.query = self.query.where( TDatasets.id_acquisition_framework.in_( self.filters.pop("id_acquisition_framework") ) ) if "geoIntersection" in self.filters: # Insersect with the geom send from the map ors = [] for str_wkt in self.filters["geoIntersection"]: # if the geom is a circle if "radius" in self.filters: radius = self.filters.pop("radius")[0] wkt = loads(str_wkt) wkt = circle_from_point(wkt, float(radius)) else: wkt = loads(str_wkt) geom_wkb = from_shape(wkt, srid=4326) ors.append(self.model.the_geom_4326.ST_Intersects(geom_wkb)) self.query = self.query.where(or_(*ors)) self.filters.pop("geoIntersection") if "period_start" in self.filters and "period_end" in self.filters: period_start = self.filters.pop("period_start")[0] period_end = self.filters.pop("period_end")[0] self.query = self.query.where( or_( func.gn_commons.is_in_period( func.date(self.model.date_min), func.to_date(period_start, "DD-MM"), func.to_date(period_end, "DD-MM"), ), func.gn_commons.is_in_period( func.date(self.model.date_max), func.to_date(period_start, "DD-MM"), func.to_date(period_end, "DD-MM"), ), ) ) if "unique_id_sinp" in self.filters: try: uuid_filter = uuid.UUID(self.filters.pop("unique_id_sinp")[0]) except ValueError as e: raise BadRequest(str(e)) self.query = self.query.where(self.model.unique_id_sinp == uuid_filter) # generic filters for colname, value in self.filters.items(): if colname.startswith("area"): self.add_join(CorAreaSynthese, CorAreaSynthese.id_synthese, self.model.id_synthese) self.query = self.query.where(CorAreaSynthese.id_area.in_(value)) elif colname.startswith("id_"): col = getattr(self.model.__table__.columns, colname) self.query = self.query.where(col.in_(value)) elif hasattr(self.model.__table__.columns, colname): col = getattr(self.model.__table__.columns, colname) if str(col.type) == "INTEGER": if colname in ["precision"]: self.query = self.query.where(col <= value[0]) else: self.query = self.query.where(col == value[0]) else: self.query = self.query.where(col.ilike("%{}%".format(value[0])))
def filter_other_filters(self): """ Other filters """ if "id_dataset" in self.filters: self.query = self.query.where( self.model.id_dataset.in_(self.filters.pop("id_dataset")) ) if "observers" in self.filters: self.query = self.query.where( self.model.observers.ilike("%" + self.filters.pop("observers")[0] + "%") ) if "id_organism" in self.filters: datasets = ( DB.session.query(CorDatasetActor.id_dataset) .filter( CorDatasetActor.id_organism.in_(self.filters.pop("id_organism")) ) .all() ) formated_datasets = [d[0] for d in datasets] self.query = self.query.where(self.model.id_dataset.in_(formated_datasets)) if "date_min" in self.filters: self.query = self.query.where( self.model.date_min >= self.filters.pop("date_min")[0] ) if "date_max" in self.filters: self.query = self.query.where( self.model.date_min <= self.filters.pop("date_max")[0] ) if "id_acquisition_framework" in self.filters: self.query = self.query.where( self.model.id_acquisition_framework.in_( self.filters.pop("id_acquisition_framework") ) ) if "geoIntersection" in self.filters: # Insersect with the geom send from the map ors = [] for str_wkt in self.filters["geoIntersection"]: # if the geom is a circle if "radius" in self.filters: radius = self.filters.pop("radius")[0] wkt = loads(str_wkt) wkt = circle_from_point(wkt, float(radius)) else: wkt = loads(str_wkt) geom_wkb = from_shape(wkt, srid=4326) ors.append(self.model.the_geom_4326.ST_Intersects(geom_wkb)) self.query = self.query.where(or_(*ors)) self.filters.pop("geoIntersection") if "period_start" in self.filters and "period_end" in self.filters: period_start = self.filters.pop("period_start")[0] period_end = self.filters.pop("period_end")[0] self.query = self.query.where( or_( func.gn_commons.is_in_period( func.date(self.model.date_min), func.to_date(period_start, "DD-MM"), func.to_date(period_end, "DD-MM"), ), func.gn_commons.is_in_period( func.date(self.model.date_max), func.to_date(period_start, "DD-MM"), func.to_date(period_end, "DD-MM"), ), ) ) # generic filters for colname, value in self.filters.items(): if colname.startswith("area"): self.add_join( CorAreaSynthese, CorAreaSynthese.id_synthese, self.model.id_synthese ) self.query = self.query.where(CorAreaSynthese.id_area.in_(value)) else: col = getattr(self.model.__table__.columns, colname) self.query = self.query.where(col.in_(value))
def _01(): search_form = SearchForm(prefix='search') g1 = aliased(OusiGuest) g2 = aliased(OusiGuest) page = request.args.get('page', 1, type=int) if current_user.role == 'admin': database = db.session.query( OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone, OusiStaff.role, g1.name.label('guest_name'), g1.month, g1.balance, func.nvl( db.session.query(g2.balance).filter( g1.name == g2.name, func.to_date(g2.month, 'yyyy-mm') == func.add_months( func.to_date(g1.month, 'yyyy-mm'), -1)), 0).label('last_balance')).filter( OusiStaff.phone == g1.staff_phone, current_user.department == OusiStaff.department, g1.month == date.today().strftime('%Y-%m')).order_by( g1.name).group_by(OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone, OusiStaff.role, g1.name.label('guest_name'), g1.month, g1.balance) if search_form.validate_on_submit(): database = db.session.query( OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone, OusiStaff.role, g1.name.label('guest_name'), g1.month, g1.balance, func.nvl( db.session.query(g2.balance).filter( g1.name == g2.name, func.to_date(g2.month, 'yyyy-mm') == func.add_months( func.to_date(g1.month, 'yyyy-mm'), -1)), 0).label('last_balance')).filter( OusiStaff.phone == g1.staff_phone, current_user.department == OusiStaff.department, and_( g1.month.between( search_form.start_time.data.strip(), search_form.end_time.data.strip()), OusiStaff.name.like('%{}%'.format( search_form.name.data.strip())), OusiStaff.phone.like('%{}%'.format( search_form.phone.data.strip())))).order_by( g1.name).group_by( OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone, OusiStaff.role, g1.name.label('guest_name'), g1.month, g1.balance) else: database = db.session.query( OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone, OusiStaff.role, g1.name.label('guest_name'), g1.month, g1.balance, func.nvl( db.session.query(g2.balance).filter( g1.name == g2.name, func.to_date(g2.month, 'yyyy-mm') == func.add_months( func.to_date(g1.month, 'yyyy-mm'), -1)), 0).label('last_balance')).filter( OusiStaff.phone == g1.staff_phone, current_user.phone == g1.staff_phone, g1.month == date.today().strftime('%Y-%m')).order_by( g1.name).group_by(OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone, OusiStaff.role, g1.name.label('guest_name'), g1.month, g1.balance) if search_form.validate_on_submit(): database = db.session.query( OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone, OusiStaff.role, g1.name.label('guest_name'), g1.month, g1.balance, func.nvl( db.session.query(g2.balance).filter( g1.name == g2.name, func.to_date(g2.month, 'yyyy-mm') == func.add_months( func.to_date(g1.month, 'yyyy-mm'), -1)), 0).label('last_balance')).filter( OusiStaff.phone == g1.staff_phone, current_user.phone == OusiStaff.phone, g1.month.between( search_form.start_time.data.strip(), search_form.end_time.data.strip())).order_by( g1.name).group_by( OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone, OusiStaff.role, g1.name.label('guest_name'), g1.month, g1.balance) data = database.paginate( page, per_page=current_app.config['OUSI_POSTS_PER_PAGE'], error_out=False) return render_template('show/01.html', data=data, searchForm=search_form, database=json.dumps(database, cls=AlchemyJsonEncoder))