示例#1
0
def compra_mensual():
    tbl_compra = Compra.mapper.mapped_table
    tbl_artcompra = ArticuloCompra.mapper.mapped_table
    tbl_articulo = Articulo.mapper.mapped_table
    tbl_marca = Marca.mapper.mapped_table

    # stmt = select([func.concat(func.year(tbl_compra.c.fecha), "-", format(func.month(tbl_compra.c.fecha), "02")).label("periodo"),
    stmt = select(
        [
            func.concat(
                func.year(tbl_compra.c.fecha), "-",
                func.month(tbl_compra.c.fecha)).label("periodo"),
            func.concat(tbl_articulo.c.descripcion, " ",
                        tbl_marca.c.denominacion, " ", tbl_articulo.c.cantidad,
                        " ", tbl_articulo.c.unidad_medida).label("articulo"),
            func.sum(tbl_artcompra.c.cantidad).label("cantidad"),
        ],
        from_obj=tbl_compra.join(tbl_artcompra).join(tbl_articulo).join(
            tbl_marca),
        group_by=[
            func.year(tbl_compra.c.fecha),
            func.month(tbl_compra.c.fecha), tbl_artcompra.c.articulo_id
        ],
    )
    return stmt.alias("compra_mensual")
def get_billing_data_per_resource_per_project(year, project_id, resource, output_type):
    if output_type == 'day':
        billing_data = db_session.query(func.unix_timestamp(Usage.usage_date), func.sum(Usage.cost), Usage.usage_value,
                                        Usage.measurement_unit). \
            filter(func.extract('year', Usage.usage_date) == year, Usage.project_id == project_id,
                   Usage.resource_type == resource). \
            group_by(func.unix_timestamp(Usage.usage_date))
    elif output_type == 'week':
        billing_data = db_session.query(Usage.project_id, func.extract(output_type, Usage.usage_date),
                                        func.sum(Usage.cost)). \
            filter(func.extract('year', Usage.usage_date) == year, Usage.project_id == project_id,
                   Usage.resource_type == resource). \
            group_by(func.month(Usage.usage_date))
    else:
        billing_data = db_session.query(func.extract(output_type, Usage.usage_date), func.sum(Usage.cost)). \
            filter(func.extract('year', Usage.usage_date) == year, Usage.project_id == project_id,
                   Usage.resource_type == resource). \
            group_by(func.month(Usage.usage_date))

    return billing_data
示例#3
0
def user_active_stats(time_based='hour'):
    """
    用户激活统计
    :return:
    """
    # 按小时统计
    if time_based == 'hour':
        start_time, end_time = get_current_day_time_ends()
        hours = get_hours(False)
        hours_zerofill = get_hours()
        result = dict(zip(hours, [0] * len(hours)))
        rows = db.session \
            .query(func.hour(User.create_time).label('hour'), func.count(User.id)) \
            .filter(User.create_time >= time_local_to_utc(start_time),
                    User.create_time <= time_local_to_utc(end_time),
                    User.status_active == STATUS_ACTIVE_OK) \
            .group_by('hour') \
            .limit(len(hours)) \
            .all()
        result.update(dict(rows))
        return [(hours_zerofill[i], result[hour]) for i, hour in enumerate(hours)]
    # 按日期统计
    if time_based == 'date':
        start_time, end_time = get_current_month_time_ends()
        today = datetime.today()
        days = get_days(year=today.year, month=today.month, zerofill=False)
        days_zerofill = get_days(year=today.year, month=today.month)
        result = dict(zip(days, [0] * len(days)))
        rows = db.session \
            .query(func.day(User.create_time).label('date'), func.count(User.id)) \
            .filter(User.create_time >= time_local_to_utc(start_time),
                    User.create_time <= time_local_to_utc(end_time),
                    User.status_active == STATUS_ACTIVE_OK) \
            .group_by('date') \
            .limit(len(days)) \
            .all()
        result.update(dict(rows))
        return [(days_zerofill[i], result[day]) for i, day in enumerate(days)]
    # 按月份统计
    if time_based == 'month':
        start_time, end_time = get_current_year_time_ends()
        months = get_months(False)
        months_zerofill = get_months()
        result = dict(zip(months, [0] * len(months)))
        rows = db.session \
            .query(func.month(User.create_time).label('month'), func.count(User.id)) \
            .filter(User.create_time >= time_local_to_utc(start_time),
                    User.create_time <= time_local_to_utc(end_time),
                    User.status_active == STATUS_ACTIVE_OK) \
            .group_by('month') \
            .limit(len(months)) \
            .all()
        result.update(dict(rows))
        return [(months_zerofill[i], result[month]) for i, month in enumerate(months)]
示例#4
0
def dqchecks_exce_oracle_excel():
    if request.method == "POST":
        start_date = request.form["start_date"]
        end_date = request.form["end_date"]
        period_select = request.form["period"]

    if period_select == "day":
        period = manifest_oracle_monitoring.file_date
    elif period_select == "month":
        period = func.month(manifest_oracle_monitoring.file_date)
    elif period_select == "year":
        period = func.year(manifest_oracle_monitoring.file_date)

    cdr_types = [
        "com", "vou", "cm", "adj", "first", "mon", "data", "voice", "sms",
        "clr"
    ]
    dates = db.session.query(period).filter(
        and_(manifest_oracle_monitoring.file_date >= start_date,
             manifest_oracle_monitoring.file_date <=
             end_date)).group_by(period).all()
    lookup = db.session.query(
        period, manifest_oracle_monitoring.cdr_type,
        func.sum(manifest_oracle_monitoring.ocs_manifest),
        func.sum(manifest_oracle_monitoring.t1_oracle),
        func.sum(manifest_oracle_monitoring.variance)).filter(
            and_(manifest_oracle_monitoring.file_date >= start_date,
                 manifest_oracle_monitoring.file_date <= end_date)).group_by(
                     period, manifest_oracle_monitoring.cdr_type).all()
    len_date = len(dates)
    dates = [d[0] for d in dates]

    cdr_dict = {}

    for l in lookup:
        if l.cdr_type not in cdr_dict.keys():
            cdr_dict[l.cdr_type] = {
                "manifest": init_list(len_date),
                "t1": init_list(len_date),
                "variance": init_list(len_date)
            }
            insert_cdr(cdr_dict[l.cdr_type], dates.index(l[0]), l[2], l[3],
                       l[4])
        else:
            insert_cdr(cdr_dict[l.cdr_type], dates.index(l[0]), l[2], l[3],
                       l[4])
示例#5
0
    def index(self):
        lst = []
        SumDoanhSoTheoNgay = db.session.query(
            func.sum(Bill.total_price).label('sum'), Bill.order_time).filter(
                func.month(Bill.order_time) ==
                datetime.datetime.today().month).group_by(
                    func.date(Bill.order_time)).all()

        # #co check status
        # SumDoanhSoTheoNgay = db.session.query(func.sum(Bill.total_price).label('sum'), Bill.order_time).filter(
        #     Bill.status == 4).filter(func.month(Bill.order_time) == datetime.datetime.today().month).group_by(
        #     func.date(Bill.order_time)).all()

        for i in SumDoanhSoTheoNgay:
            lst.append([i[1].day, int(i[0])])
        lst.sort(key=myFunc)
        return self.render('admin/analytics.html', lst=lst)
示例#6
0
    def update_forecasts(self, acc):
        session = object_session(acc)

        start_date = session.query(func.min(Transaction.rdate)).filter(
            Transaction.id_account == acc.id).one()[0]
        if start_date is None:
            return

        # Start from a full month.
        if start_date.day > 4:
            start_date = next_month(start_date)

        start_date = max(start_date, (datetime.date.today() -
                                      timedelta(days=6 * 31)).replace(day=1))

        records = acc.transactions.filter(
            Transaction.rdate >= start_date).order_by(Transaction.rdate).all()

        if len(records) == 0:
            return

        if not session.domain in self.main_cats:
            self.build_categories_cache(session)

        # Calculate clusters
        clusterer = TransactionClusterer(records,
                                         self.main_cats[session.domain])
        clusterer.find_clusters(acc, datetime.date.today())

        # do not consider unplanned transactions
        for c in acc.clusters.filter(TransactionsCluster.next_date != None):
            #self.logger.debug('%%%% update_projection iteration on cluster %s' % c.wording.encode('utf-8', 'replace'))
            cluster_records = c.transactions
            ids = [tr.id for tr in cluster_records]
            cluster = clusterer.find_cluster(ids)
            if cluster is not None:
                #self.logger.debug('cluster found')
                if cluster.add_records(
                        cluster_records
                ) >= 1 and cluster.next_date <= datetime.date.today():
                    # if the cluster was not full, we should retry to find any missing transaction
                    cluster.find_missing_transaction(acc,
                                                     datetime.date.today())
                    cluster.refresh()
            elif c.enabled:
                #self.logger.debug('cluster not found')
                cluster = clusterer.add_old_cluster(c, cluster_records, acc,
                                                    datetime.date.today())

            if cluster is None:
                c.enabled = False
            else:
                clusterer.clusters.remove(cluster)
                if c.enabled:
                    c.mean_amount = cluster.mean_amount
                    c.median_increment = cluster.median_increment
                    c.next_date = cluster.next_date
                    c.wording = cluster.wording
                    c.id_category = cluster.category_id

                    for tr in cluster.records:
                        if tr.id_cluster is None:
                            tr.id_cluster = c.id

        for cluster in clusterer.clusters:
            c = TransactionsCluster(id_account=acc.id,
                                    mean_amount=cluster.mean_amount,
                                    median_increment=cluster.median_increment,
                                    next_date=cluster.next_date,
                                    wording=cluster.wording,
                                    id_category=cluster.category_id)
            session.add(c)
            session.flush()
            for record in cluster.records:
                record.id_cluster = c.id

        # Check if there is enough data to calculate prediction.
        count = session.query(func.count('*').label('nb')).filter(
            Transaction.id_account == acc.id).group_by(
                func.year(Transaction.rdate), func.month(Transaction.rdate))
        avg = session.query(func.avg(count.subquery().columns.nb)).scalar()
        if avg is None or avg < 10:
            for d in xrange(31):
                session.merge(Prediction(id_account=acc.id, day=d))
        else:
            # Calculate prediction
            prediction = PredictionCalculator()
            prediction.add_transactions(
                records, ignore_after=datetime.date.today().replace(day=1))
            prediction.compute_averages()
            for d in xrange(31):
                mean_amount, std_amount = prediction.get_prediction_still_to_be_spent(
                    d + 1)
                session.merge(
                    Prediction(id_account=acc.id,
                               day=d,
                               mean_amount=mean_amount,
                               std_amount=std_amount))
示例#7
0
def sales_orders_order_stats(time_based='hour'):
    """
    报价成交统计
    :return:
    """
    condition = [SalesOrder.status_order == STATUS_ORDER_OK]
    # 按小时统计
    if time_based == 'hour':
        start_time, end_time = get_current_day_time_ends()
        hours = get_hours(False)
        hours_zerofill = get_hours()
        result = dict(zip(hours, [0] * len(hours)))
        condition.extend(
            [
                SalesOrder.create_time >= time_local_to_utc(start_time),
                SalesOrder.create_time <= time_local_to_utc(end_time)
            ]
        )
        rows = db_bearing.session \
            .query(func.hour(SalesOrder.create_time).label('hour'), func.count(SalesOrder.id)) \
            .filter(*condition) \
            .group_by('hour') \
            .limit(len(hours)) \
            .all()
        result.update(dict(rows))
        return [(hours_zerofill[i], result[hour]) for i, hour in enumerate(hours)]
    # 按日期统计
    if time_based == 'date':
        start_time, end_time = get_current_month_time_ends()
        today = datetime.today()
        days = get_days(year=today.year, month=today.month, zerofill=False)
        days_zerofill = get_days(year=today.year, month=today.month)
        result = dict(zip(days, [0] * len(days)))
        condition.extend(
            [
                SalesOrder.create_time >= time_local_to_utc(start_time),
                SalesOrder.create_time <= time_local_to_utc(end_time)
            ]
        )
        rows = db_bearing.session \
            .query(func.day(SalesOrder.create_time).label('date'), func.count(SalesOrder.id)) \
            .filter(*condition) \
            .group_by('date') \
            .limit(len(days)) \
            .all()
        result.update(dict(rows))
        return [(days_zerofill[i], result[day]) for i, day in enumerate(days)]
    # 按月份统计
    if time_based == 'month':
        start_time, end_time = get_current_year_time_ends()
        months = get_months(False)
        months_zerofill = get_months()
        result = dict(zip(months, [0] * len(months)))
        condition.extend(
            [
                SalesOrder.create_time >= time_local_to_utc(start_time),
                SalesOrder.create_time <= time_local_to_utc(end_time)
            ]
        )
        rows = db_bearing.session \
            .query(func.month(SalesOrder.create_time).label('month'), func.count(SalesOrder.id)) \
            .filter(*condition) \
            .group_by('month') \
            .limit(len(months)) \
            .all()
        result.update(dict(rows))
        return [(months_zerofill[i], result[month]) for i, month in enumerate(months)]
示例#8
0
def supplier_end_user_stats(time_based='hour'):
    """
    终端客户统计
    :return:
    """
    condition = [Supplier.company_type == TYPE_COMPANY_FINAL_USER]
    # 按小时统计
    if time_based == 'hour':
        start_time, end_time = get_current_day_time_ends()
        hours = get_hours(False)
        hours_zerofill = get_hours()
        result = dict(zip(hours, [0] * len(hours)))
        condition.extend(
            [
                Supplier.create_time >= time_local_to_utc(start_time),
                Supplier.create_time <= time_local_to_utc(end_time)
            ]
        )
        rows = db.session \
            .query(func.hour(Supplier.create_time).label('hour'), func.count(Supplier.id)) \
            .filter(*condition) \
            .group_by('hour') \
            .limit(len(hours)) \
            .all()
        result.update(dict(rows))
        return [(hours_zerofill[i], result[hour]) for i, hour in enumerate(hours)]
    # 按日期统计
    if time_based == 'date':
        start_time, end_time = get_current_month_time_ends()
        today = datetime.today()
        days = get_days(year=today.year, month=today.month, zerofill=False)
        days_zerofill = get_days(year=today.year, month=today.month)
        result = dict(zip(days, [0] * len(days)))
        condition.extend(
            [
                Supplier.create_time >= time_local_to_utc(start_time),
                Supplier.create_time <= time_local_to_utc(end_time)
            ]
        )
        rows = db.session \
            .query(func.day(Supplier.create_time).label('date'), func.count(Supplier.id)) \
            .filter(*condition) \
            .group_by('date') \
            .limit(len(days)) \
            .all()
        result.update(dict(rows))
        return [(days_zerofill[i], result[day]) for i, day in enumerate(days)]
    # 按月份统计
    if time_based == 'month':
        start_time, end_time = get_current_year_time_ends()
        months = get_months(False)
        months_zerofill = get_months()
        result = dict(zip(months, [0] * len(months)))
        condition.extend(
            [
                Supplier.create_time >= time_local_to_utc(start_time),
                Supplier.create_time <= time_local_to_utc(end_time)
            ]
        )
        rows = db.session \
            .query(func.month(Supplier.create_time).label('month'), func.count(Supplier.id)) \
            .filter(*condition) \
            .group_by('month') \
            .limit(len(months)) \
            .all()
        result.update(dict(rows))
        return [(months_zerofill[i], result[month]) for i, month in enumerate(months)]
示例#9
0
    def dashboard():

        #next_page = request.args.get('next')
        '''
        Agenda.query.with_entities(
                func.sum(Agenda.valor_servico).label('valor')
            ).filter(Agenda.confirmado==1).join(Servico.agenda)
        '''
        #saldo das contas
        sql = text('''SELECT DESCRICAO, SUM(VALOR) AS TOTAL FROM  (
                       SELECT
                        CONT.DESCRICAO,
                        SUM(LANC.VALOR_REALIZADO) AS VALOR
                    FROM LANCAMENTOS AS LANC
                    LEFT JOIN CONTAS AS CONT ON LANC.CONTAS_ID = CONT.ID
                    WHERE TIPO_MOVIMENTACAO = 'C'
                    GROUP BY CONT.DESCRICAO
                    UNION
                    SELECT
                        CONT.DESCRICAO,
                        SUM(LANC.VALOR_REALIZADO)*-1 AS VALOR
                    FROM LANCAMENTOS AS LANC
                    LEFT JOIN CONTAS AS CONT ON LANC.CONTAS_ID = CONT.ID
                    WHERE TIPO_MOVIMENTACAO = 'D'
                    GROUP BY CONT.DESCRICAO
                    ) AS A
                GROUP BY DESCRICAO
                ''')

        result = db.engine.execute(sql)
        saldo_contas = [row for row in result]

        # movimentações realizadas
        realizado_debito = Lancamentos.query.with_entities(
            Lancamentos.descricao, Lancamentos.data_efetivacao,
            func.sum(Lancamentos.valor_realizado).label('total_realizado'),
            func.month(Lancamentos.data_efetivacao).label('mes')).filter(
                Lancamentos.tipo_movimentacao == 'D',
                Lancamentos.data_efetivacao != None).group_by(
                    Lancamentos.descricao, Lancamentos.data_efetivacao).all()

        realizado_credito = Lancamentos.query.with_entities(
            Lancamentos.descricao, Lancamentos.data_efetivacao,
            func.sum(Lancamentos.valor_realizado).label('total_realizado'),
            func.month(Lancamentos.data_efetivacao).label('mes')).filter(
                Lancamentos.tipo_movimentacao == 'C',
                Lancamentos.data_efetivacao != None).group_by(
                    Lancamentos.descricao, Lancamentos.data_efetivacao).all()
        ######################################

        # movimentações previstas
        previsto_debito = Lancamentos.query.with_entities(
            Lancamentos.descricao, Lancamentos.data_prevista,
            func.sum(Lancamentos.valor_previsto).label('total_previsto'),
            func.month(Lancamentos.data_prevista).label('mes')).filter(
                Lancamentos.tipo_movimentacao == 'D',
                Lancamentos.data_efetivacao == None).group_by(
                    Lancamentos.descricao, Lancamentos.data_prevista).all()

        previsto_credito = Lancamentos.query.with_entities(
            Lancamentos.descricao, Lancamentos.data_prevista,
            func.sum(Lancamentos.valor_previsto).label('total_previsto'),
            func.month(Lancamentos.data_prevista).label('mes')).filter(
                Lancamentos.tipo_movimentacao == 'C',
                Lancamentos.data_efetivacao == None).group_by(
                    Lancamentos.descricao, Lancamentos.data_prevista).all()
        ######################################
        '''
        previsto_total = Lancamentos.query.with_entities(
            Lancamentos.descricao,
            Lancamentos.data_prevista,
            func.sum(Lancamentos.valor_previsto).label('total_previsto')
        ).group_by(Lancamentos.descricao,
            Lancamentos.data_prevista).all()
        '''

        return render_template('fluxocaixa/dashboard.html',
                               titulo='Fluxo caixa',
                               previsto_debito=previsto_debito,
                               previsto_credito=previsto_credito,
                               realizado_debito=realizado_debito,
                               realizado_credito=realizado_credito,
                               saldo_contas=saldo_contas)
示例#10
0
def dqchecks_overview_oracle_js():
    if request.method == "POST":
        start_date = request.form["start_date"]
        end_date = request.form["end_date"]
        period_select = request.form["period"]
    elif request.method == "GET":
        date_today = date.today()
        start_date = date_today - relativedelta(months=4)
        end_date = date_today
        period_select = "day"

    if period_select == "day":
        period = manifest_oracle_monitoring.file_date
    elif period_select == "month":
        period = func.month(manifest_oracle_monitoring.file_date)
    elif period_select == "year":
        period = func.year(manifest_oracle_monitoring.file_date)

    dates = db.session.query(period).filter(
        and_(manifest_oracle_monitoring.file_date >= start_date,
             manifest_oracle_monitoring.file_date <=
             end_date)).group_by(period).all()
    variances = db.session.query(
        period, manifest_oracle_monitoring.cdr_type,
        func.sum(manifest_oracle_monitoring.variance)).filter(
            and_(manifest_oracle_monitoring.file_date >= start_date,
                 manifest_oracle_monitoring.file_date <= end_date)).group_by(
                     period, manifest_oracle_monitoring.cdr_type).all()
    len_date = len(dates)

    date_list = init_list(len_date)
    variance_com = init_list(len_date)
    variance_vou = init_list(len_date)
    variance_first = init_list(len_date)
    variance_mon = init_list(len_date)
    variance_cm = init_list(len_date)
    variance_adj = init_list(len_date)
    variance_data = init_list(len_date)
    variance_voice = init_list(len_date)
    variance_sms = init_list(len_date)
    variance_clr = init_list(len_date)

    for i, d in enumerate(dates):
        date_list[i] = format_date(d[0], period_select)
        for v in variances:
            if v.cdr_type == "com" and v[0] == d[0]:
                variance_com[i] = str(v[2])
            elif v.cdr_type == "vou" and v[0] == d[0]:
                variance_vou[i] = str(v[2])
            elif v.cdr_type == "first" and v[0] == d[0]:
                variance_first[i] = str(v[2])
            elif v.cdr_type == "mon" and v[0] == d[0]:
                variance_mon[i] = str(v[2])
            elif v.cdr_type == "cm" and v[0] == d[0]:
                variance_cm[i] = str(v[2])
            elif v.cdr_type == "adj" and v[0] == d[0]:
                variance_adj[i] = str(v[2])
            elif v.cdr_type == "data" and v[0] == d[0]:
                variance_data[i] = str(v[2])
            elif v.cdr_type == "voice" and v[0] == d[0]:
                variance_voice[i] = str(v[2])
            elif v.cdr_type == "sms" and v[0] == d[0]:
                variance_sms[i] = str(v[2])
            elif v.cdr_type == "clr" and v[0] == d[0]:
                variance_clr[i] = str(v[2])

    result_set = {
        "date_list": date_list,
        "variance_com": variance_com,
        "variance_vou": variance_vou,
        "variance_first": variance_first,
        "variance_mon": variance_mon,
        "variance_cm": variance_cm,
        "variance_adj": variance_adj,
        "variance_data": variance_data,
        "variance_voice": variance_voice,
        "variance_sms": variance_sms,
        "variance_clr": variance_clr,
    }

    return jsonify(result_set)
示例#11
0
def dqchecks_hive_excel():
    if request.method == "POST":
        start_date = request.form["start_date"]
        end_date = request.form["end_date"]
        period_select = request.form["period"]

    if period_select == "day":
        period = manifest_hive_monitoring.file_date
    elif period_select == "month":
        period = func.month(manifest_hive_monitoring.file_date)
    elif period_select == "year":
        period = func.year(manifest_hive_monitoring.file_date)

    cdr_types = [
        "com", "vou", "cm", "adj", "first", "mon", "data", "voice", "sms",
        "clr"
    ]
    dates = db.session.query(period).filter(
        and_(manifest_hive_monitoring.file_date >= start_date,
             manifest_hive_monitoring.file_date <=
             end_date)).group_by(period).all()
    lookup = db.session.query(
        period, manifest_hive_monitoring.cdr_type,
        func.sum(manifest_hive_monitoring.ocs_manifest),
        func.sum(manifest_hive_monitoring.t1_hive),
        func.sum(manifest_hive_monitoring.variance)).filter(
            and_(manifest_hive_monitoring.file_date >= start_date,
                 manifest_hive_monitoring.file_date <= end_date)).group_by(
                     period, manifest_hive_monitoring.cdr_type).all()
    len_date = len(dates)
    dates = [d[0] for d in dates]

    cdr_dict = {}

    for l in lookup:
        if l.cdr_type not in cdr_dict.keys():
            cdr_dict[l.cdr_type] = {
                "manifest": init_list(len_date),
                "t1": init_list(len_date),
                "variance": init_list(len_date)
            }
            insert_cdr(cdr_dict[l.cdr_type], dates.index(l[0]), l[2], l[3],
                       l[4])
        else:
            insert_cdr(cdr_dict[l.cdr_type], dates.index(l[0]), l[2], l[3],
                       l[4])

    #output in bytes
    output = io.BytesIO()
    #create WorkBook object
    workbook = Workbook()
    workbook_name = "Manifest Hive Bashing {}".format(
        datetime.now().strftime("%Y-%m-%d %H-%M-%S"))
    #add a sheet
    ws = workbook.create_sheet('Manifest vs T1 Bashing Validation', 0)

    greenFill = PatternFill(start_color='AEEA00',
                            end_color='AEEA00',
                            fill_type='solid')

    x_pos = 1
    y_pos = 1
    temp_y = 1
    x_lim = 19
    row = 0

    for c in cdr_dict.keys():
        ### CREATE THE HEADER FOR THE CDR
        # Merge for Date Column
        ws.cell(row=y_pos, column=x_pos,
                value="Date").alignment = Alignment(horizontal='center')
        # ws.cell(row=y_pos, column=x_pos, value="Date").fill = greenFill
        ws.merge_cells(start_row=y_pos,
                       start_column=x_pos,
                       end_row=y_pos + 1,
                       end_column=x_pos)
        # Merge for CDR Row
        ws.cell(row=y_pos, column=x_pos + 1,
                value=c).alignment = Alignment(horizontal='center')
        ws.merge_cells(start_row=y_pos,
                       start_column=x_pos + 1,
                       end_row=y_pos,
                       end_column=x_pos + 3)
        ws.cell(row=y_pos + 1, column=x_pos + 1,
                value="MANIFEST").alignment = Alignment(horizontal='center')
        ws.cell(row=y_pos + 1, column=x_pos + 2,
                value="T1").alignment = Alignment(horizontal='center')
        ws.cell(row=y_pos + 1, column=x_pos + 3,
                value="VARIANCE").alignment = Alignment(horizontal='center')
        for i, d in enumerate(dates):
            ws.cell(row=y_pos + 2, column=x_pos, value=d.strftime("%m/%d/%y"))
            ws.cell(row=y_pos + 2,
                    column=x_pos + 1,
                    value=cdr_dict[c]["manifest"][i])
            ws.cell(row=y_pos + 2,
                    column=x_pos + 2,
                    value=cdr_dict[c]["t1"][i])
            ws.cell(row=y_pos + 2,
                    column=x_pos + 3,
                    value=cdr_dict[c]["variance"][i])
            y_pos += 1

        ### SET X AND Y POSITIONS
        if x_pos + 3 < 19:
            y_pos = temp_y
            x_pos += 5
        else:
            row += 1
            y_pos = temp_y + (len(dates) + 3)
            temp_y = y_pos
            x_pos = 1

    workbook.save(output)
    output.seek(0)
    pprint.pprint(cdr_dict)
    filename = workbook_name

    return Response(
        output,
        mimetype=
        "application/openxmlformats-officedocument.spreadsheetml.sheet",
        headers={
            "Content-Disposition":
            "attachment;filename={}.xlsx".format(filename)
        })
示例#12
0
def payroll_report():
    """
    display payroll report from all database records
    """
    # get first two weeks of month  1st -> 15th
    biweekly_first = db.session.query(Worklog.employee_id,
                      func.year(Worklog.date)+'/'+func.month(Worklog.date)+'/1 - '+\
                      func.year(Worklog.date)+'/'+func.month(Worklog.date)+'/15',
                      func.sum(Worklog.hours * Payscale.hourly_rate))\
                .filter(extract('day', Worklog.date) < 16 )\
                .join(Payscale, Worklog.job_group==Payscale.job_group)\
                .order_by(Worklog.employee_id, Worklog.date)\
                .group_by(Worklog.employee_id,
                          func.year(Worklog.date),
                          func.month(Worklog.date))

    # get second two weeks of month, 16th -> 'monthend' (correct day# inserted later)
    biweekly_second = db.session.query(Worklog.employee_id,
                      func.year(Worklog.date)+'/'+func.month(Worklog.date)+'/16 - '+\
                      func.year(Worklog.date)+'/'+func.month(Worklog.date)+'/monthend',
                      func.sum(Worklog.hours * Payscale.hourly_rate))\
                .filter(extract('day', Worklog.date) > 15 )\
                .join(Payscale, Worklog.job_group==Payscale.job_group)\
                .order_by(Worklog.employee_id, Worklog.date)\
                .group_by(Worklog.employee_id,
                          func.year(Worklog.date),
                          func.month(Worklog.date))

    # combine them and for second two weeks put in the correct monthend
    records_all = []

    # first half of month is already correct (1st -> 15th)
    for r in biweekly_first:
        records_all.append(r)

    # total days for each month (leap year will be considered later)
    days_in_month = {
        '1': '31',
        '2': '28',
        '3': '31',
        '4': '30',
        '5': '31',
        '6': '30',
        '7': '31',
        '8': '31',
        '9': '30',
        '10': '31',
        '11': '30',
        '12': '31'
    }

    # second half of month needs a little more to handle 'monthend'
    regex = re.compile('^(\d+)\/.*\/(\d+)\/monthend$')
    for r in biweekly_second:
        pay_period = r[1]
        year_month = regex.findall(pay_period)
        if len(year_month) > 0:
            [(year, month)] = year_month
            num_days = days_in_month[month]
            if month == '2' and isleap(int(year)):
                num_days = '29'
            # create record with correct monthend day#
            r = (r[0], r[1].replace('monthend', num_days), r[2])

        records_all.append(r)

    return render_template('report.html',
                           n_rows=len(records_all),
                           report_data=records_all)