Beispiel #1
0
 def visit(self, func_name):
     from sqlalchemy.sql import func, case
     func_entry = {
         'entry': {
             'next_year': lambda x: func.TIMESTAMPADD(text('year'), self.visit_param(1), x),
             'next_quarter': lambda x: func.TIMESTAMPADD(text('quarter'), self.visit_param(1), x),
             'next_month': lambda x: func.TIMESTAMPADD(text('month'), self.visit_param(1), x),
             'next_day': lambda x: func.TIMESTAMPADD(text('day'), self.visit_param(1), x),
             'cast': lambda x, y: func.cast(x, y),
             'cast_day': lambda x: func.cast(x, get_type('date')),
             'cast_month': lambda x: self.visit('concat')(
                 self.visit('year')(x), self.visit_param('-'), self.visit('month')(x)
             ),
             'cast_quarter': lambda x: self.visit('concat')(
                 self.visit('year')(x), self.visit('quarter')(x), self.visit_param('Q')
             ),
             'cast_year': lambda x: self.visit('year')(x)
         },
         'oracle': {
             'if': lambda x, y, z: case([(x, y)], else_=z),
             'concat': lambda *args: reduce(func.concat, args),
             'year': lambda x: func.to_char(x, self.visit_param('yyyy')),
             'month': lambda x: func.to_char(x, self.visit_param('mm')),
             'day': lambda x: func.to_char(x, self.visit_param('dd'))
         },
         'postgresql': {
             'if': lambda x, y, z: case([(x, y)], else_=z),
             'year': lambda x: func.to_char(x, self.visit_param('yyyy')),
             'month': lambda x: func.to_char(x, self.visit_param('mm')),
             'day': lambda x: func.to_char(x, self.visit_param('dd'))
         },
         'db2': {
             'concat': lambda *args: reduce(func.concat, args),
             'if': lambda x, y, z: case([(x, y)], else_=z)
         },
         'hana': {
             'if': lambda x, y, z: case([(x, y)], else_=z),
             'concat': lambda *args: reduce(func.concat, args),
             'year': lambda x: func.to_char(x, self.visit_param('yyyy')),
             'month': lambda x: func.to_char(x, self.visit_param('mm')),
             'day': lambda x: func.to_char(x, self.visit_param('dd'))
         },
         'mssql': {
             'if': lambda x, y, z: case([(x, y)], else_=z),
             'concat': lambda *args: reduce(lambda a, b: a+b, map(
                 lambda x: func.cast(x, get_type('string')), args
             ))
         }
     }
Beispiel #2
0
def selectReportMonthDetails(userId, year, month, startDay, endDay):
    """選択された月の日別とその稼働時間を取得するDB処理

    :param userId: 登録ユーザID
    :param year: 登録年
    :param month: 登録月
    :param startDay: 月の初日
    :param endDay: 月の最後の日
    """
    subq1 = db.session.query(
        func.generate_series(
            func.date(startDay) - func.CURRENT_DATE(),
            func.date(endDay) - func.CURRENT_DATE()).label('i')).subquery()

    subq2 = db.session.query(
        func.cast(func.date_part('day',
                                 func.CURRENT_DATE() + subq1.c.i),
                  Integer).label('day')).subquery()

    monthDetails = db.session.query(
        subq2.c.day,
        __model.rest_flg,
        db.func.to_char(
            __model.normal_working_hours + __model.overtime_hours +
            __model.holiday_work_hours, '999D99').label('workTime'),
    ).outerjoin(
        __model,
        and_(subq2.c.day == __model.work_day, __model.user_id == userId,
             __model.work_year == year,
             __model.work_month == month)).order_by(subq2.c.day).all()

    return monthDetails
def analyze_day(day = "2014-08-22"):
    hour = func.cast(func.substr(trips.c.joreohitusaika_time,1,2), sa.Integer)
    cols = [trips.c.tulopysakki, hour, func.avg(trips.c.ohitusaika_ero).label('delay_avg')]
    new_conds = conditions
    new_conds.append(trips.c.tapahtumapaiva==day)
    conds = and_(*new_conds)
    groupcols = [trips.c.tulopysakki, hour]
    ts = run(cols, conds, groupcols, n_limit=None)
    save_multi_json(ts, "../site/hourly_stop_delays_%s.json" % day)
def get_stop_time_matrix():
    weekhour = func.strftime('%w', trips.c.tapahtumapaiva) * 24 + func.cast(func.substr(trips.c.tuloaika_time,1,2), sa.Integer)
    cols = [trips.c.tulopysakki,
            weekhour,
            func.sum(trips.c.pysakkiaika).label('stop_sum')]
    conds = and_(*conditions)
    groupcols = [trips.c.tulopysakki, weekhour]
    ts = run(cols, conds, groupcols, n_limit=None)
    # Write to a csv file
    stops = list(set([val[0] for val in ts]))
    stop_map = {v: idx for (idx, v) in enumerate(stops)}
    mat = np.zeros((168, len(stops)))
    for (stop, wh, val) in ts:
        mat[wh, stop_map[stop]] = val
    with open('../site/stop_time_matrix.csv', 'w') as f:
        f.write(",".join(map(str, stops)) + '\n')
        for i in range(mat.shape[0]):
            f.write(",".join(map(str, mat[i,:])) + '\n')
Beispiel #5
0
def assign_order():
    if request.method == "GET":
        techs = getTech()
        return render_template("order/assign_order.html", techs = techs)
    else:
        serial = request.form.get("serial")
        place = request.form.get("place")
        tech_code = request.form.get("tech")
        
        selectStatment = device_essentials.select().where(device_essentials.c.serial == serial
              ).where(device_essentials.c.status == "operational").limit(1)
        selectStatment = db.execute(selectStatment)
        device = selectStatment.fetchone()
        
        if device is None:
            return apology("Device serial is wrong or device is scraped", 400)
        
        table = ppm_map[device[2]]
        
        insert1 = order_essentials.insert().values(serial = serial, place = place,
                type = device[2], department = session.get("department"),
                tech_code = tech_code, date_issued = func.cast(func.now(), DATE))
        try:
            db.execute(insert1)
        except sqlalchemy.exc.IntegrityError:
            print("Error duplicate enteries")
            return apology("Can't enter duplicate enteries", 403)
        
        sel_command = order_essentials.select().with_only_columns([func.max(order_essentials.c.code)]
          ).where(order_essentials.c.serial == serial).limit(1)
        sel_command = db.execute(sel_command)
        r_code = sel_command.fetchone()[0]
        
        insert2 = table.insert().values(r_code = r_code)
        db.execute(insert2)
        
        return render_template("control/main.html", message = "Order assigned successfully")
Beispiel #6
0
def removeDevice():
    reportDictionary = {}
    
    code = request.form.get("code")
    reportDictionary.update({"code" : code})
    cause = request.form.get("cause")
    reportDictionary.update({"cause" : cause})
    
    reportDictionary.update({"date" : func.cast(func.now(), DATE)})
    
    updateDevice1 = device_essentials.update().where(device_essentials.c.code == code).values(status = "obselete")
    updateDevice2 = device_extras.update().where(device_extras.c.d_code == code).values(remove_date = func.cast(func.now(), DATE))
    
    selDevice = device_essentials.select().with_only_columns([device_essentials.c.serial,
        device_essentials.c.type]).where(device_essentials.c.code == code).limit(1)
    selDevice = db.execute(selDevice)
    rows = selDevice.fetchone()
    reportDictionary.update({"device_serial" : rows[0]})
    reportDictionary.update({"device_type" : rows[1]})
    
    selDevice = device_extras.select().with_only_columns([device_extras.c.name,
          device_extras.c.manufacturer]).where(device_extras.c.d_code == code).limit(1)
    selDevice = db.execute(selDevice)
    rows = selDevice.fetchone()
    reportDictionary.update({"device_name" : rows[0]})
    reportDictionary.update({"device_manufacturer" : rows[1]})
    
    insertReport = report_scrap.insert().values(**reportDictionary)
    
    delete_maint = maintain_dates.delete().where(maintain_dates.c.device_code == code)
    
    db.execute(updateDevice1)
    db.execute(updateDevice2)
    db.execute(insertReport)
    db.execute(delete_maint)
    
    return None
Beispiel #7
0
def submit_order():
    if request.method == "GET":
        order_id = request.args.get("id")
        
        rows = []
        
        # check if a wrong argument is passed
        if order_id is None:
            return apology("Invalid request", 400)
        try:
            order_id = int(order_id)
        except:
            return apology("Invalid request", 400)
        
        tech_code = db.execute(users_tech.select().with_only_columns([users_tech.c['r_code']]
             ).where(users_tech.c['username'] == session.get("username"))).fetchone()[0]
        
        selOrder = order_essentials.select().where(order_essentials.c.tech_code == tech_code).where(
        order_essentials.c.date_responded == None).where(order_essentials.c.code == order_id).limit(1)
        selOrder = db.execute(selOrder)
        order_ess = selOrder.fetchone()
        
        serial = order_ess[1]
        place = order_ess[2]
        device_type = order_ess[3]
        date_issued = order_ess[6].strftime('%d-%m-%Y')
        
        device_code = db.execute(device_essentials.select().with_only_columns([device_essentials.c['code']]
             ).where(device_essentials.c['serial'] == serial)).fetchone()[0]
        
        tech_name = db.execute(tech_essentials.select().with_only_columns([tech_essentials.c['name']]
             ).where(tech_essentials.c['code'] == order_ess[5])).fetchone()[0]

        
        if order_ess is None:
            return apology("Order not found", 404)
        
        extras_table = ppm_map[device_type]
        order_extra = extras_table.c.keys()
        
        size = len(order_extra) - 1
        for index in range(1,size):
            rows.append([order_extra[index].replace("_"," "), order_extra[index]])
        
        return render_template("order/submit_order.html", serial = serial, place = place,
                device_type = device_type, device_code = device_code, date_issued = date_issued,
                tech_name = tech_name, order_id = order_id, rows = rows)
    
    
    elif request.method == "POST":
        code = request.form.get("code")
        device_type = request.form.get("device_type")
        device_code = request.form.get("device_code")
        
        updateDictionary = {}
        
        ignored_keys = ["code","notes","device_code","device_type"]
        for key in request.form.keys():
            if key not in ignored_keys:
                updateDictionary.update({key : func.cast(1, Integer)})

        up_command = order_essentials.update().where(order_essentials.c.code 
                == code).values(date_responded = func.cast(func.now(), DATE))
        db.execute(up_command)
        
        extras_table = ppm_map[device_type]
        
        if len(updateDictionary) != 0:
            up_command = extras_table.update().where(extras_table.c.r_code == code).values(**updateDictionary)
            db.execute(up_command)
        
        up_command = maintain_dates.update().where(maintain_dates.c.device_code == device_code
               ).values(maint_date = datetime.date.today() + datetime.timedelta(days = 30))
        db.execute(up_command)
        
        session["message"] = "Order submitted successfully"
        
        return redirect("/due_orders")
Beispiel #8
0
def update_entries(session, date, logger=None):
    """Create receiver coverage stats for Melissas ognrange."""

    if logger is None:
        logger = app.logger

    logger.info("Compute receiver coverages.")

    (start, end) = date_to_timestamps(date)

    # Filter aircraft beacons
    sq = (session.query(
        AircraftBeacon.location_mgrs_short, AircraftBeacon.receiver_id,
        AircraftBeacon.signal_quality, AircraftBeacon.altitude,
        AircraftBeacon.device_id).filter(
            and_(between(AircraftBeacon.timestamp, start,
                         end), AircraftBeacon.location_mgrs_short != null(),
                 AircraftBeacon.receiver_id != null(),
                 AircraftBeacon.device_id != null())).subquery())

    # ... and group them by reduced MGRS, receiver and date
    query = (session.query(
        sq.c.location_mgrs_short,
        sq.c.receiver_id,
        func.cast(date, Date).label("date"),
        func.max(sq.c.signal_quality).label("max_signal_quality"),
        func.min(sq.c.altitude).label("min_altitude"),
        func.max(sq.c.altitude).label("max_altitude"),
        func.count(sq.c.altitude).label("aircraft_beacon_count"),
        func.count(func.distinct(sq.c.device_id)).label("device_count"),
    ).group_by(sq.c.location_mgrs_short, sq.c.receiver_id).subquery())

    # if a receiver coverage entry exist --> update it
    upd = (update(ReceiverCoverage).where(
        and_(
            ReceiverCoverage.location_mgrs_short ==
            query.c.location_mgrs_short,
            ReceiverCoverage.receiver_id == query.c.receiver_id,
            ReceiverCoverage.date == date)).values({
                "max_signal_quality":
                query.c.max_signal_quality,
                "min_altitude":
                query.c.min_altitude,
                "max_altitude":
                query.c.max_altitude,
                "aircraft_beacon_count":
                query.c.aircraft_beacon_count,
                "device_count":
                query.c.device_count,
            }))

    result = session.execute(upd)
    update_counter = result.rowcount
    session.commit()
    logger.debug(
        "Updated receiver coverage entries: {}".format(update_counter))

    # if a receiver coverage entry doesnt exist --> insert it
    new_coverage_entries = session.query(query).filter(~exists().where(
        and_(
            ReceiverCoverage.location_mgrs_short ==
            query.c.location_mgrs_short, ReceiverCoverage.receiver_id ==
            query.c.receiver_id, ReceiverCoverage.date == date)))

    ins = insert(ReceiverCoverage).from_select(
        (
            ReceiverCoverage.location_mgrs_short,
            ReceiverCoverage.receiver_id,
            ReceiverCoverage.date,
            ReceiverCoverage.max_signal_quality,
            ReceiverCoverage.min_altitude,
            ReceiverCoverage.max_altitude,
            ReceiverCoverage.aircraft_beacon_count,
            ReceiverCoverage.device_count,
        ),
        new_coverage_entries,
    )

    result = session.execute(ins)
    insert_counter = result.rowcount
    session.commit()

    finish_message = "ReceiverCoverage: {} inserted, {} updated".format(
        insert_counter, update_counter)
    logger.debug(finish_message)
    return finish_message
Beispiel #9
0
    "bigquery": aggregations_bigquery,
}


#######################
# Conversions are a callable on a column expression that yields a
# nonaggregated column expression
# for instance, quarter(sales_date) => func.date_trunc('quarter', MyTable.sales_date)
#######################

conversions = {
    "month": lambda fld: func.date_trunc("month", fld),
    "week": lambda fld: func.date_trunc("week", fld),
    "year": lambda fld: func.date_trunc("year", fld),
    "quarter": lambda fld: func.date_trunc("quarter", fld),
    "string": lambda fld: func.cast(fld, String()),
    "int": lambda fld: func.cast(fld, Integer()),
}


conversions_redshift = {
    # age doesn't work on all databases
    "age": lambda fld: postgres_age(fld),
}

conversions_bigquery = {
    "month": lambda fld: func.date_trunc(fld, text("month")),
    "week": lambda fld: func.date_trunc(fld, text("week")),
    "year": lambda fld: func.date_trunc(fld, text("year")),
    "quarter": lambda fld: func.date_trunc(fld, text("quarter")),
    "age": lambda fld: bq_age(fld),
Beispiel #10
0
def query_work_day_stats(
    company_id,
    start_date=None,
    end_date=None,
    first=None,
    after=None,
    tzname="Europe/Paris",
):
    tz = gettz(tzname)
    if after:
        max_time, user_id_ = parse_datetime_plus_id_cursor(after)
        max_date = max_time.date()
        end_date = min(max_date, end_date) if end_date else max_date

    query = (Activity.query.join(Mission).join(
        Expenditure,
        and_(
            Activity.user_id == Expenditure.user_id,
            Activity.mission_id == Expenditure.mission_id,
        ),
        isouter=True,
    ).with_entities(
        Activity.id,
        Activity.user_id,
        Activity.mission_id,
        Mission.name,
        Activity.start_time,
        Activity.end_time,
        Activity.type,
        Expenditure.id.label("expenditure_id"),
        Expenditure.type.label("expenditure_type"),
        func.generate_series(
            func.date_trunc(
                "day",
                func.timezone(
                    tzname,
                    func.timezone("UTC", Activity.start_time),
                ),
            ),
            func.timezone(
                tzname,
                func.coalesce(
                    func.timezone("UTC", Activity.end_time),
                    func.now(),
                ),
            ),
            "1 day",
        ).label("day"),
    ).filter(
        Mission.company_id == company_id,
        ~Activity.is_dismissed,
        Activity.start_time != Activity.end_time,
    ))

    query = _apply_time_range_filters(
        query,
        to_datetime(start_date, tz_for_date=tz),
        to_datetime(end_date,
                    tz_for_date=tz,
                    convert_dates_to_end_of_day_times=True),
    )

    has_next_page = False
    if first:
        activity_first = max(first * 5, 200)
        query = query.order_by(desc("day"), desc(
            Activity.user_id)).limit(activity_first + 1)
        has_next_page = query.count() > activity_first

    query = query.subquery()

    query = (db.session.query(query).group_by(
        query.c.user_id, query.c.day, query.c.mission_id,
        query.c.name).with_entities(
            query.c.user_id.label("user_id"),
            query.c.day,
            func.timezone("UTC",
                          func.timezone(tzname,
                                        query.c.day)).label("utc_day_start"),
            query.c.mission_id.label("mission_id"),
            query.c.name.label("mission_name"),
            func.min(
                func.greatest(
                    query.c.start_time,
                    func.timezone("UTC", func.timezone(tzname, query.c.day)),
                )).label("start_time"),
            func.max(
                func.least(
                    func.timezone(
                        "UTC",
                        func.timezone(
                            tzname,
                            query.c.day + func.cast("1 day", Interval)),
                    ),
                    func.coalesce(query.c.end_time, func.now()),
                )).label("end_time"),
            func.bool_or(
                and_(
                    query.c.end_time.is_(None),
                    query.c.day == func.current_date(),
                )).label("is_running"),
            *[
                func.sum(
                    case(
                        [(
                            query.c.type == a_type.value,
                            extract(
                                "epoch",
                                func.least(
                                    func.timezone(
                                        "UTC",
                                        func.timezone(
                                            tzname,
                                            query.c.day +
                                            func.cast("1 day", Interval),
                                        ),
                                    ),
                                    func.coalesce(query.c.end_time,
                                                  func.now()),
                                ) - func.greatest(
                                    query.c.start_time,
                                    func.timezone(
                                        "UTC",
                                        func.timezone(tzname, query.c.day),
                                    ),
                                ),
                            ),
                        )],
                        else_=0,
                    )).label(f"{a_type.value}_duration")
                for a_type in ActivityType
            ],
            func.greatest(func.count(distinct(query.c.expenditure_id)),
                          1).label("n_exp_dups"),
            func.count(distinct(query.c.id)).label("n_act_dups"),
            *[
                func.sum(
                    case(
                        [(query.c.expenditure_type == e_type.value, 1)],
                        else_=0,
                    )).label(f"n_{e_type.value}_expenditures")
                for e_type in ExpenditureType
            ],
        ).subquery())

    query = (db.session.query(query).group_by(
        query.c.user_id, query.c.day).with_entities(
            query.c.user_id.label("user_id"),
            query.c.day,
            func.array_agg(distinct(
                query.c.mission_name)).label("mission_names"),
            func.min(query.c.start_time).label("start_time"),
            func.max(query.c.end_time).label("end_time"),
            func.bool_or(query.c.is_running).label("is_running"),
            *[
                func.sum(
                    getattr(query.c, f"{a_type.value}_duration") /
                    query.c.n_exp_dups).cast(Integer).label(
                        f"{a_type.value}_duration") for a_type in ActivityType
            ],
            *[
                func.sum(
                    getattr(query.c, f"n_{e_type.value}_expenditures") /
                    query.c.n_act_dups).cast(Integer).label(
                        f"n_{e_type.value}_expenditures")
                for e_type in ExpenditureType
            ],
        ).order_by(desc("day"), desc("user_id")).subquery())

    query = db.session.query(query).with_entities(
        *query.c,
        extract("epoch", query.c.end_time -
                query.c.start_time).label("service_duration"),
        reduce(
            lambda a, b: a + b,
            [
                getattr(query.c, f"{a_type.value}_duration")
                for a_type in ActivityType
            ],
        ).label("total_work_duration"),
    )

    results = query.all()
    if after:
        results = [
            r for r in results if r.day.date() < max_date or (
                r.day.date() == max_date and r.user_id < user_id_)
        ]

    if first:
        if has_next_page:
            # The last work day may be incomplete because we didn't fetch all the activities => remove it
            results = results[:-1]
        if len(results) > first:
            results = results[:first]
            has_next_page = True

    return results, has_next_page
Beispiel #11
0
    def GetResponseRate(self, request, context):
        with session_scope() as session:
            # this subquery gets the time that the request was sent
            t = (select(Message.conversation_id, Message.time).where(
                Message.message_type == MessageType.chat_created).subquery())
            # this subquery gets the time that the user responded to the request
            s = (select(Message.conversation_id,
                        func.min(Message.time).label("time")).where(
                            Message.author_id == request.user_id).group_by(
                                Message.conversation_id).subquery())

            res = session.execute(
                select(
                    User.id,
                    # number of requests received
                    func.count().label("n"),
                    # percentage of requests responded to
                    (func.count(s.c.time) /
                     func.cast(func.greatest(func.count(t.c.time), 1.0), Float)
                     ).label("response_rate"),
                    # the 33rd percentile response time
                    percentile_disc(0.33).within_group(
                        func.coalesce(s.c.time - t.c.time,
                                      timedelta(days=1000))
                    ).label("response_time_p33"),
                    # the 66th percentile response time
                    percentile_disc(0.66).within_group(
                        func.coalesce(s.c.time - t.c.time,
                                      timedelta(days=1000))
                    ).label("response_time_p66"),
                ).where_users_visible(context).where(
                    User.id == request.user_id).outerjoin(
                        HostRequest,
                        HostRequest.host_user_id == User.id).outerjoin(
                            t, t.c.conversation_id ==
                            HostRequest.conversation_id).outerjoin(
                                s, s.c.conversation_id ==
                                HostRequest.conversation_id).group_by(
                                    User.id)).one_or_none()

            if not res:
                context.abort(grpc.StatusCode.NOT_FOUND, errors.USER_NOT_FOUND)

            _, n, response_rate, response_time_p33, response_time_p66 = res

            if n < 3:
                return requests_pb2.GetResponseRateRes(
                    insufficient_data=requests_pb2.
                    ResponseRateInsufficientData(), )

            if response_rate <= 0.33:
                return requests_pb2.GetResponseRateRes(
                    low=requests_pb2.ResponseRateLow(), )

            response_time_p33_coarsened = Duration_from_timedelta(
                timedelta(
                    seconds=round(response_time_p33.total_seconds() / 60) *
                    60))

            if response_rate <= 0.66:
                return requests_pb2.GetResponseRateRes(
                    some=requests_pb2.ResponseRateSome(
                        response_time_p33=response_time_p33_coarsened), )

            response_time_p66_coarsened = Duration_from_timedelta(
                timedelta(
                    seconds=round(response_time_p66.total_seconds() / 60) *
                    60))

            if response_rate <= 0.90:
                return requests_pb2.GetResponseRateRes(
                    most=requests_pb2.ResponseRateMost(
                        response_time_p33=response_time_p33_coarsened,
                        response_time_p66=response_time_p66_coarsened), )
            else:
                return requests_pb2.GetResponseRateRes(
                    almost_all=requests_pb2.ResponseRateAlmostAll(
                        response_time_p33=response_time_p33_coarsened,
                        response_time_p66=response_time_p66_coarsened), )