예제 #1
0
def balance_sheet(currency, period):
    periods = db.session \
        .query(\
            func.date_part('year', models.LedgerEntries.date), \
            func.date_part('month', models.LedgerEntries.date)) \
        .group_by( \
            func.date_part('year', models.LedgerEntries.date), \
            func.date_part('month', models.LedgerEntries.date)) \
        .all()
    periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
    
    if period == 'Current':
        period = datetime.now()
    else:
        period = datetime.strptime(period, "%Y-%m")
    lastday = calendar.monthrange(period.year, period.month)[1]
    year = period.year
    month = period.month
    period = datetime(year, month, lastday, 23, 59, 59)
    
    period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
    period_end = datetime(period.year, period.month, lastday, 23, 59, 59, 999999)

    elements = db.session \
        .query(models.Elements) \
        .join(models.Classifications) \
        .join(models.Accounts) \
        .join(models.Subaccounts) \
        .all()

    retained_earnings = 0

    for element in elements:
        element.balance = 0
        for classification in element.classifications:
            classification.balance = 0
            for account in classification.accounts:
                account.balance = 0
                for subaccount in account.subaccounts:
                    subaccount.balance = 0
                    subaccount.ledgerentries = [c for c in subaccount.ledgerentries if c.date <= period_end ]
                    for entry in subaccount.ledgerentries:
                        if entry.currency == currency:
                            element.balance -= entry.credit
                            classification.balance -= entry.credit
                            account.balance -= entry.credit
                            subaccount.balance -= entry.credit
                            element.balance += entry.debit
                            classification.balance += entry.debit
                            account.balance += entry.debit
                            subaccount.balance += entry.debit
        if element.name == 'Equity':
            retained_earnings =  -element.balance
    elements = [c for c in elements if c.name in ['Assets', 'Liabilities']]
    return render_template('balance_sheet.html', 
    periods=periods,
    currency=currency,
    elements=elements,
    retained_earnings=retained_earnings,
    period=period_end)
예제 #2
0
class Offender(db.Model):
    __tablename__ = 'offenders'
    id = db.Column(db.Integer, primary_key=True)
    tdcj_id = db.Column(db.Integer)
    last_name = db.Column(db.String(50))
    first_name = db.Column(db.String(50))
    age = db.Column(db.Integer)
    dob = db.Column(db.Date)
    execution_num = db.Column(db.Integer)
    execution_date = db.Column(db.Date)
    execution_year = func.date_part('year', execution_date)
    execution_month = func.date_part('month', execution_date)
    execution_day = db.Column(db.Integer)
    offense_date = db.Column(db.Date)
    offense_county = db.Column(db.String(50))
    received_date = db.Column(db.Date)
    gender = db.Column(db.String(1))
    race = db.Column(db.String(20))
    last_statement = db.Column(db.Text)
    teaser = db.Column(db.Text)
    info_url = db.Column(db.String(200))
    statement_url = db.Column(db.String(200))

    def __init__(self,
                 id=None,
                 tdcj_id=None,
                 last_name=None,
                 first_name=None,
                 age=None,
                 dob=None,
                 execution_num=None,
                 execution_date=None,
                 offense_date=None,
                 offense_county=None,
                 received_date=None,
                 gender=None,
                 race=None,
                 last_statement=None,
                 info_url=None,
                 statement_url=None):
        self.tdcj_id = tdcj_id
        self.last_name = last_name
        self.first_name = first_name
        self.age = age
        self.dob = dob
        self.execution_num = execution_num
        self.execution_date = execution_date
        self.execution_day = doy_leap(execution_date)
        self.offense_date = offense_date
        self.offense_county = offense_county
        self.received_date = received_date
        self.gender = gender
        self.race = race
        self.last_statement = last_statement
        self.info_url = info_url
        self.statement_url = statement_url
예제 #3
0
def balance_sheet_historical(currency, period):
    periods = db.session \
        .query(\
            func.date_part('year', models.LedgerEntries.date), \
            func.date_part('month', models.LedgerEntries.date)) \
        .group_by( \
            func.date_part('year', models.LedgerEntries.date), \
            func.date_part('month', models.LedgerEntries.date)) \
        .all()
    periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
    period = datetime.strptime(period, "%Y-%m")
    lastday = calendar.monthrange(period.year, period.month)[1]
    period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
    period_end = datetime(period.year, period.month, lastday, 23, 59, 59, 999999)

    elements = db.session \
        .query(models.Elements) \
        .join(models.Classifications) \
        .join(models.Accounts) \
        .join(models.Subaccounts) \
        .all()

    retained_earnings = 0

    for element in elements:
        element.balance = 0
        for classification in element.classifications:
            classification.balance = 0
            for account in classification.accounts:
                account.balance = 0
                for subaccount in account.subaccounts:
                    subaccount.balance = 0
                    subaccount.ledgerentries = [c for c in subaccount.ledgerentries if c.date <= period_end ]
                    for ledger_entry in subaccount.ledgerentries:
                        if ledger_entry.currency == currency:
                            
                            if ledger_entry.tside == 'credit':
                                element.balance -= ledger_entry.amount
                                classification.balance -= ledger_entry.amount
                                account.balance -= ledger_entry.amount
                                subaccount.balance -= ledger_entry.amount
                            elif ledger_entry.tside == 'debit':
                                element.balance += ledger_entry.amount
                                classification.balance += ledger_entry.amount
                                account.balance += ledger_entry.amount
                                subaccount.balance += ledger_entry.amount
        if element.name == 'Equity':
            retained_earnings =  -element.balance
            print(retained_earnings)
    elements = [c for c in elements if c.name in ['Assets', 'Liabilities']]
    return render_template('financial_statements/balance_sheet.html', 
    periods=periods,
    currency=currency,
    elements=elements,
    retained_earnings=retained_earnings,
    period=period_end)
예제 #4
0
파일: views.py 프로젝트: enikesha/pacioli
def balance_sheet(currency):
    periods = db.session \
        .query(\
            func.date_part('year', models.LedgerEntries.date), \
            func.date_part('month', models.LedgerEntries.date)) \
        .group_by( \
            func.date_part('year', models.LedgerEntries.date), \
            func.date_part('month', models.LedgerEntries.date)) \
        .all()
    periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
    period = datetime.now()
    period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
    period_end = datetime(period.year, period.month, period.day, 23, 59, 59, 999999)

    elements = db.session \
        .query(models.Elements) \
        .join(models.Classifications) \
        .join(models.Accounts) \
        .join(models.Subaccounts) \
        .all()

    retained_earnings = 0
    
    for element in elements:
        element.balance = 0
        for classification in element.classifications:
            classification.balance = 0
            for account in classification.accounts:
                account.balance = 0
                for subaccount in account.subaccounts:
                    subaccount.balance = 0
                    subaccount.ledgerentries = [c for c in subaccount.ledgerentries if c.date <= period_end ]
                    for ledger_entry in subaccount.ledgerentries:
                        if ledger_entry.currency == currency:
                            
                            if ledger_entry.tside == 'credit':
                                element.balance -= ledger_entry.amount
                                classification.balance -= ledger_entry.amount
                                account.balance -= ledger_entry.amount
                                subaccount.balance -= ledger_entry.amount
                            elif ledger_entry.tside == 'debit':
                                element.balance += ledger_entry.amount
                                classification.balance += ledger_entry.amount
                                account.balance += ledger_entry.amount
                                subaccount.balance += ledger_entry.amount
        if element.name == 'Equity':
            retained_earnings =  -element.balance
            print(retained_earnings)
    elements = [c for c in elements if c.name in ['Assets', 'Liabilities']]
    return render_template('financial_statements/balance_sheet.html', 
    periods=periods,
    currency=currency,
    elements=elements,
    retained_earnings=retained_earnings,
    period=period_end)
예제 #5
0
def get_years(model):
    if model == "distinct":
        q = DB.session.query(
            label("year", distinct(func.date_part("year", VSynthese.date_min)))
        ).order_by("year")
    if model == "min-max":
        q = DB.session.query(
            func.min(func.date_part("year", VSynthese.date_min)),
            func.max(func.date_part("year", VSynthese.date_min)),
        )
    return q.all()
예제 #6
0
def statement_of_cash_flows(currency, period):
    periods = db.session \
        .query(\
            func.date_part('year', models.LedgerEntries.date), \
            func.date_part('month', models.LedgerEntries.date)) \
        .group_by( \
            func.date_part('year', models.LedgerEntries.date), \
            func.date_part('month', models.LedgerEntries.date)) \
        .all()
    periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
    if period == 'Current':
        period = datetime.now()
        lastday = period.day
    else:
        period = datetime.strptime(period, "%Y-%m")
        lastday = calendar.monthrange(period.year, period.month)[1]
    period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
    period_end = datetime(period.year, period.month, lastday, 23, 59, 59, 999999)

    elements = db.session \
        .query(models.Elements) \
        .join(models.Classifications) \
        .filter(models.Classifications.name.in_(['Revenues', 'Expenses', 'Gains', 'Losses']))\
        .join(models.Accounts) \
        .join(models.Subaccounts) \
        .all()
    net_income = 0
    for element in elements:
        element.classifications = [c for c in element.classifications if c.name in ['Revenues', 'Expenses', 'Gains', 'Losses']]
        for classification in element.classifications:
            classification.balance = 0
            for account in classification.accounts:
                account.balance = 0
                for subaccount in account.subaccounts:
                    subaccount.balance = 0
                    subaccount.ledgerentries = [c for c in subaccount.ledgerentries if period_beg <= c.date <= period_end ]
                    for ledger_entry in subaccount.ledgerentries:
                        if ledger_entry.currency == currency:
                            
                            if ledger_entry.tside == 'credit':
                                classification.balance -= ledger_entry.amount
                                account.balance -= ledger_entry.amount
                                subaccount.balance -= ledger_entry.amount
                            elif ledger_entry.tside == 'debit':
                                classification.balance += ledger_entry.amount
                                account.balance += ledger_entry.amount
                                subaccount.balance += ledger_entry.amount
    return render_template('financial_statements/statement_of_cash_flows.html',
            period = period,
            periods = periods,
            currency = currency,
            elements = elements,
            net_income = net_income)
예제 #7
0
def income_statement(currency, period):
    periods = db.session \
        .query(\
            func.date_part('year', models.LedgerEntries.date), \
            func.date_part('month', models.LedgerEntries.date)) \
        .group_by( \
            func.date_part('year', models.LedgerEntries.date), \
            func.date_part('month', models.LedgerEntries.date)) \
        .all()
    periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
    
    if period == 'Current':
        period = datetime.now()
    else:
        period = datetime.strptime(period, "%Y-%m")
    lastday = calendar.monthrange(period.year, period.month)[1]
    year = period.year
    month = period.month
    period = datetime(year, month, lastday, 23, 59, 59)
    
    period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
    period_end = datetime(period.year, period.month, lastday, 23, 59, 59, 999999)

    elements = db.session \
        .query(models.Elements) \
        .join(models.Classifications) \
        .filter(models.Classifications.name.in_(['Revenues', 'Expenses', 'Gains', 'Losses']))\
        .join(models.Accounts) \
        .join(models.Subaccounts) \
        .all()
    net_income = 0
    for element in elements:
        element.classifications = [c for c in element.classifications if c.name in ['Revenues', 'Expenses', 'Gains', 'Losses']]
        for classification in element.classifications:
            for account in classification.accounts:
                for subaccount in account.subaccounts:
                    subaccount.total = 0
                    subaccount.ledgerentries = [c for c in subaccount.ledgerentries if period_beg <= c.date <= period_end ]
                    for ledger_entry in subaccount.ledgerentries:
                        if ledger_entry.currency == currency:
                            net_income += ledger_entry.credit
                            subaccount.total += ledger_entry.credit
                            net_income -= ledger_entry.debit
                            subaccount.total -= ledger_entry.debit
    return render_template('income_statement.html',
            title = 'Income Statement',
            periods = periods,
            currency = currency,
            elements = elements,
            net_income = net_income,
            period=period)
예제 #8
0
def get_synthese_stat():
    params = request.args
    q = DB.session.query(
        label("year", func.date_part("year", VSynthese.date_min)),
        func.count(VSynthese.id_synthese),
        func.count(distinct(VSynthese.cd_ref)),
    ).group_by("year")
    if ("selectedRegne" in params) and (params["selectedRegne"] != ""):
        q = q.filter(VSynthese.regne == params["selectedRegne"])
    if ("selectedPhylum" in params) and (params["selectedPhylum"] != ""):
        q = q.filter(VSynthese.phylum == params["selectedPhylum"])
    if "selectedClasse" in params and (params["selectedClasse"] != ""):
        q = q.filter(VSynthese.classe == params["selectedClasse"])
    if "selectedOrdre" in params and (params["selectedOrdre"] != ""):
        q = q.filter(VSynthese.ordre == params["selectedOrdre"])
    if "selectedFamille" in params and (params["selectedFamille"] != ""):
        q = q.filter(VSynthese.famille == params["selectedFamille"])
    if ("selectedGroup2INPN"
            in params) and (params["selectedGroup2INPN"] != ""):
        q = q.filter(VSynthese.group2_inpn == params["selectedGroup2INPN"])
    if ("selectedGroup1INPN"
            in params) and (params["selectedGroup1INPN"] != ""):
        q = q.filter(VSynthese.group1_inpn == params["selectedGroup1INPN"])
    if ("taxon" in params) and (params["taxon"] != ""):
        q = q.filter(VSynthese.cd_ref == params["taxon"])
    return q.all()
예제 #9
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
예제 #10
0
def income_statement(currency):
    periods = db.session \
        .query(\
            func.date_part('year', models.LedgerEntries.date),\
            func.date_part('month', models.LedgerEntries.date)) \
        .group_by( \
            func.date_part('year', models.LedgerEntries.date),\
            func.date_part('month', models.LedgerEntries.date)) \
        .all()
    periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
    period = datetime.now()
    period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
    period_end = datetime(period.year, period.month, period.day, 23, 59, 59, 999999)

    elements = db.session \
        .query(models.Elements) \
        .join(models.Classifications) \
        .filter(models.Classifications.name.in_(['Revenues', 'Expenses', 'Gains', 'Losses']))\
        .join(models.Accounts) \
        .join(models.Subaccounts) \
        .all()
    net_income = 0
    for element in elements:
        element.classifications = [c for c in element.classifications if c.name in ['Revenues', 'Expenses', 'Gains', 'Losses']]
        for classification in element.classifications:
            for account in classification.accounts:
                for subaccount in account.subaccounts:
                    subaccount.total = 0
                    subaccount.ledgerentries = [c for c in subaccount.ledgerentries if period_beg <= c.date <= period_end ]
                    for ledger_entry in subaccount.ledgerentries:
                        if ledger_entry.currency == currency:
                            if ledger_entry.tside == 'credit':
                                subaccount.total += ledger_entry.amount
                                net_income += ledger_entry.amount
                            elif ledger_entry.tside == 'debit':
                                net_income -= ledger_entry.amount
                                subaccount.total -= ledger_entry.amount
    return render_template('financial_statements/income_statement.html',
            title = 'Income Statement',
            periods = periods,
            currency = currency,
            elements = elements,
            net_income = net_income)
예제 #11
0
def ledger_page(accountName, currency, groupby, interval):
    if groupby == "Daily":
        interval = datetime.strptime(interval, "%m-%d-%Y")
        year = interval.year
        month = interval.month
        day = interval.day
        ledger_entries = models.LedgerEntries \
            .query \
            .filter_by(ledger=accountName) \
            .filter_by(currency=currency) \
            .filter( \
                func.date_part('year', models.LedgerEntries.date)==year, \
                func.date_part('month', models.LedgerEntries.date)==month, \
                func.date_part('day', models.LedgerEntries.date)==day) \
            .order_by(models.LedgerEntries.date) \
            .order_by(models.LedgerEntries.tside.asc()) \
            .all()
        account = ledgers.foot_account(accountName, ledger_entries, 'All')
    if groupby == "Monthly":
        interval = datetime.strptime(interval, "%m-%Y")
        year = interval.year
        month = interval.month
        ledger_entries = models.LedgerEntries\
            .query\
            .filter_by(ledger=accountName) \
            .filter_by(currency=currency) \
            .filter( \
                func.date_part('year', models.LedgerEntries.date)==year, \
                func.date_part('month', models.LedgerEntries.date)==month)\
            .order_by(models.LedgerEntries.date) \
            .order_by(models.LedgerEntries.tside.desc()) \
            .all()
        account = ledgers.foot_account(accountName, ledger_entries, 'All')
    return render_template('bookkeeping/ledger.html',
        title = 'Ledger',
        account=account,
        ledger_entries=ledger_entries,
        groupby2 = groupby,
        groupby = 'All',
        accountName=accountName,
        interval=interval,
        currency=currency)
예제 #12
0
    def posts_per_hour(self, start_date=None, end_date=None):
        q = select([func.date_part('hour', t_posts.c.created_at),
                    func.count(t_posts.c.id)],
                    t_posts.c.user_id==self.id).group_by('1').order_by('1')

        if start_date: q = q.where(t_posts.c.created_at>=start_date)
        if end_date: q = q.where(t_posts.c.created_at<end_date)

        data = dict(meta.Session.execute(q).fetchall())
        hours = dict([(hour, 0) for hour in range(0, 24)])
        return dict(hours, **data).values()
예제 #13
0
    def posts_per_dow(self, start_date=None, end_date=None):
        q = select([func.date_part('isodow', t_posts.c.created_at),
                    func.count(t_posts.c.id)],
                    t_posts.c.user_id==self.id).group_by('1').order_by('1')

        if start_date: q = q.where(t_posts.c.created_at>=start_date)
        if end_date: q = q.where(t_posts.c.created_at<end_date)

        data = dict(meta.Session.execute(q).fetchall())
        days = dict([(day, 0) for day in range(1, 8)])
        return dict(days, **data).values()
예제 #14
0
    def posts_per_hour(self, start_date=None, end_date=None):
        q = select([
            func.date_part('hour', t_posts.c.created_at),
            func.count(t_posts.c.id)
        ], t_posts.c.user_id == self.id).group_by('1').order_by('1')

        if start_date: q = q.where(t_posts.c.created_at >= start_date)
        if end_date: q = q.where(t_posts.c.created_at < end_date)

        data = dict(meta.Session.execute(q).fetchall())
        hours = dict([(hour, 0) for hour in range(0, 24)])
        return dict(hours, **data).values()
예제 #15
0
    def posts_per_dow(self, start_date=None, end_date=None):
        q = select([
            func.date_part('isodow', t_posts.c.created_at),
            func.count(t_posts.c.id)
        ], t_posts.c.user_id == self.id).group_by('1').order_by('1')

        if start_date: q = q.where(t_posts.c.created_at >= start_date)
        if end_date: q = q.where(t_posts.c.created_at < end_date)

        data = dict(meta.Session.execute(q).fetchall())
        days = dict([(day, 0) for day in range(1, 8)])
        return dict(days, **data).values()
예제 #16
0
def get_synthese_per_tax_level_stat(taxLevel):
    params = request.args
    if taxLevel == "Règne":
        q = (DB.session.query(
            func.coalesce(VSynthese.regne, "Not defined"),
            func.count(VSynthese.id_synthese),
        ).group_by(VSynthese.regne).order_by(VSynthese.regne))
    if taxLevel == "Phylum":
        q = (DB.session.query(
            func.coalesce(VSynthese.phylum, "Not defined"),
            func.count(VSynthese.id_synthese),
        ).group_by(VSynthese.phylum).order_by(VSynthese.phylum))
    if taxLevel == "Classe":
        q = (DB.session.query(
            func.coalesce(VSynthese.classe, "Not defined"),
            func.count(VSynthese.id_synthese),
        ).group_by(VSynthese.classe).order_by(VSynthese.classe))
    if taxLevel == "Ordre":
        q = (DB.session.query(
            func.coalesce(VSynthese.ordre, "Not defined"),
            func.count(VSynthese.id_synthese),
        ).group_by(VSynthese.ordre).order_by(VSynthese.ordre))
    if taxLevel == "Groupe INPN 1":
        q = (DB.session.query(
            func.coalesce(VSynthese.group1_inpn, "Not defined"),
            func.count(VSynthese.id_synthese),
        ).group_by(VSynthese.group1_inpn).order_by(VSynthese.group1_inpn))
    if taxLevel == "Groupe INPN 2":
        q = (DB.session.query(
            func.coalesce(VSynthese.group2_inpn, "Not defined"),
            func.count(VSynthese.id_synthese),
        ).group_by(VSynthese.group2_inpn).order_by(VSynthese.group2_inpn))
    if "selectedYearRange" in params:
        yearRange = params["selectedYearRange"].split(",")
        q = q.filter(
            func.date_part("year", VSynthese.date_min) <= yearRange[1])
        q = q.filter(
            func.date_part("year", VSynthese.date_max) >= yearRange[0])
    return q.all()
예제 #17
0
def trial_balance_historical(currency, groupby, period):
    accountsQuery = db.session \
        .query(models.LedgerEntries.ledger) \
        .group_by(models.LedgerEntries.ledger) \
        .filter(models.LedgerEntries.currency==currency) \
        .all()
    periods = db.session \
        .query(\
            func.date_part('year', models.LedgerEntries.date) + '-'+
            func.date_part('month', models.LedgerEntries.date)) \
        .group_by(\
            func.date_part('year', models.LedgerEntries.date),\
            func.date_part('month', models.LedgerEntries.date)) \
        .filter(models.LedgerEntries.currency==currency) \
        .all()
    period = datetime.strptime(period, "%Y-%m")
    year = period.year
    month = period.month
    day = calendar.monthrange(year, month)[1]
    period = datetime(year, month, day, 23, 59, 59)
    accounts = []
    totalDebits = 0
    totalCredits = 0
    for accountResult in accountsQuery:
        accountName = accountResult[0]
        ledger_entries = models.LedgerEntries \
            .query \
            .filter_by(ledger=accountName) \
            .filter_by(currency=currency) \
            .filter( \
                func.date_part('year', models.LedgerEntries.date)==year, \
                func.date_part('month', models.LedgerEntries.date)==month) \
            .order_by(models.LedgerEntries.date) \
            .order_by(models.LedgerEntries.tside.desc()) \
            .all()
        query = ledgers.foot_account(accountName, ledger_entries, 'All')
        totalDebits += query['debitBalance']
        totalCredits += query['creditBalance']
        accounts.append(query)
    return render_template('bookkeeping/trial_balance.html',
        currency=currency,
        periods=periods,
        period=period,
        accounts=accounts,
        totalDebits=totalDebits,
        totalCredits=totalCredits)
예제 #18
0
파일: _select.py 프로젝트: bryand1/snippets
To access built-in functions provided by the database we use func object.
The following listing shows how to use date/time, mathematical and string
functions found in PostgreSQL database.
"""

from sqlalchemy.sql import func

c = [

    ##  date/time functions  ##

    func.timeofday(),
    func.localtime(),
    func.current_timestamp(),
    func.date_part("month", func.now()),
    func.now(),

    ##  mathematical functions  ##

    func.pow(4,2),
    func.sqrt(441),
    func.pi(),
    func.floor(func.pi()),
    func.ceil(func.pi()),

    ##  string functions  ##

    func.lower("ABC"),
    func.upper("abc"),
    func.length("abc"),
예제 #19
0
                    continue
            logger.debug("len(required):".format(len(required)))

            # add data to table
            if len(required) > 1:
                logger.debug("ADD TO HOURLY TABLE")
                rows_added += add_data_hourly(required)
            else:
                logger.debug("TOO LESS DATA, cant add")
                break
    logger.info("{}.{}.{} rows_added: {}".format(g.name, d.name, t.name,
                                                 rows_added))
    return rows_added


m = func.date_part('minute', cast(Data.stime, types.DateTime)).label('m')
h = func.date_part('hour', cast(Data.stime, types.DateTime)).label('h')

# Here define gateway, device and tags to add hourly procedure
gw_list = session.query(Gateway).all()
device_list = session.query(Device).all()
tag_list = session.query(Tag).filter(
    Tag.name.ilike('%import%') & ~Tag.name.ilike('%part%')).all()

if __name__ == '__main__':
    logger.info("Start process...")
    for i, t in enumerate(tag_list):
        logger.debug("Tags count:{} {}".format(i + 1, t.name))

    rows_count = 0
    for g in gw_list:
예제 #20
0
파일: main.py 프로젝트: zehome/claritick
     # on the 'X' axis
     criteria_mapping = {
         'second' : ('minute', 'Minute'),
         'minute' : ('hour', 'Hour'),
         'hour'   : ('day', 'Day'),
         'dow'    : ('week', 'Week'),
         'day'    : ('month', 'Month'),
         'week'   : ('year', 'Year'),
         'month'  : ('year', 'Year'),
         }
     rebuilded_select = []
     # TODO : Sûrement améliorable :
     for index, statement in enumerate(self.select):
         first_base_column = list(statement.base_columns)[0]
         if hasattr(first_base_column, 'name') and first_base_column.name == 'date_trunc':
             statement = func.date_part(self.granularity, statement).label(self.granularity)
             extract_criteria = criteria_mapping.get(self.granularity, ('year', u'Année'))
             self.group_by.append(extract(extract_criteria[0], self.date_criteria_field))
             rebuilded_select.append(statement)
             rebuilded_select.append(cast(extract(extract_criteria[0], self.date_criteria_field), Integer()).label(extract_criteria[1]))
         else:
             rebuilded_select.append(statement)
     self.select = rebuilded_select
 # Here is what we have generated so far
 self.logger.debug("SELECT %s JOIN %s FILTERS %s %s %s GROUP BY %s",self.select,self.joins,self.start_filter, self.end_filter, self.filters,self.group_by)
 # Alias identically named columns
 already_seen_names = []
 new_select = []
 for column in self.select:
     if column.key in already_seen_names:
         new_select.append(column.label('%s_%s' % (column.parententity.mapped_table.name, column.key)))
예제 #21
0
파일: ledgers.py 프로젝트: enikesha/pacioli
def query_entries(accountName, groupby, currency):
    if groupby == "All":
        ledger_entries = models.LedgerEntries \
            .query \
            .filter_by(ledger=accountName) \
            .filter_by(currency=currency) \
            .order_by(models.LedgerEntries.date.desc()) \
            .order_by(models.LedgerEntries.tside.desc()) \
            .all()
        account = foot_account(accountName, ledger_entries, 'All')
    elif groupby == "Daily":
        debit_ledger_entries = db.session\
            .query( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.date_part('day', models.LedgerEntries.date), \
                func.sum(models.LedgerEntries.amount)) \
            .filter_by(ledger=accountName)\
            .filter_by(tside='debit')\
            .filter_by(currency=currency)\
            .group_by( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.date_part('day', models.LedgerEntries.date)) \
            .all()
        credit_ledger_entries = db.session \
            .query( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.date_part('day', models.LedgerEntries.date), \
                func.sum(models.LedgerEntries.amount))\
            .filter_by(currency=currency) \
            .filter_by(ledger=accountName) \
            .filter_by(tside='credit') \
            .group_by( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.date_part('day', models.LedgerEntries.date)) \
            .all()
        ledger_entries = {}
        for entry in debit_ledger_entries:
            day = datetime.date(int(entry[0]), int(entry[1]), int(entry[2]))
            if not day in ledger_entries:
                ledger_entries[day] = {}
            ledger_entries[day]['debit'] = int(entry[3])
        for entry in credit_ledger_entries:
            day = datetime.date(int(entry[0]), int(entry[1]), int(entry[2]))
            if not day in ledger_entries:
              ledger_entries[day] = {}
            ledger_entries[day]['credit'] = int(entry[3])
        ledger_entries = OrderedDict(sorted(ledger_entries.items()))
        account = foot_account(accountName, ledger_entries, 'Summary')
    elif groupby == "Monthly":
        debit_ledger_entries = db.session \
            .query( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.sum(models.LedgerEntries.amount)) \
            .filter_by(ledger=accountName) \
            .filter_by(currency=currency) \
            .filter_by(tside='debit') \
            .group_by( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date)) \
            .all()
        credit_ledger_entries = db.session\
            .query( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.sum(models.LedgerEntries.amount)) \
            .filter_by(ledger=accountName) \
            .filter_by(currency=currency) \
            .filter_by(tside='credit') \
            .group_by( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date)) \
            .all()
        ledger_entries = {}
        for entry in debit_ledger_entries:
            month = datetime.date(int(entry[0]), int(entry[1]), 1)
            if not month in ledger_entries:
                ledger_entries[month] = {}
            ledger_entries[month]['debit'] = int(entry[2])
        for entry in credit_ledger_entries:
            month = datetime.date(int(entry[0]), int(entry[1]), 1)
            if not month in ledger_entries:
                ledger_entries[month] = {}
            ledger_entries[month]['credit'] = int(entry[2])
        ledger_entries = OrderedDict(sorted(ledger_entries.items()))
        account = foot_account(accountName, ledger_entries, 'Summary')
    return [account, ledger_entries]
예제 #22
0
                              maxBytes=20 * 1024 * 1024,
                              backupCount=5,
                              encoding=None,
                              delay=0)
handler.setFormatter(formatter)
logger = logging.getLogger(__name__)
logger.addHandler(handler)
logger.setLevel(logging.DEBUG)

from pgsql_hourly import Data, Device, Tag, Base, Hourly, Daily, session, \
                         gw_list, device_list, tag_list

from sqlalchemy import types, and_, select
from sqlalchemy.sql import func, cast

hh = func.date_part('hour', cast(Hourly.stime, types.DateTime)).label('h')
dd = func.date_part('day', cast(Hourly.stime, types.DateTime)).label('d')

import dateutil.parser


def my_tstamp_strip(t):
    return dateutil.parser.parse(t).date().isoformat()
    #datetime.strptime(t,"%Y-%m-%d %H:%M:%S.%f").strftime("%Y-%m-%d %H:00")


def get_daily_last_row_stime():
    # get last row time stamp
    last_record = session.query(Daily).filter(
        and_(
            Daily.gateway == g,
예제 #23
0
def get_ledger(subaccount, currency, groupby, period_beg=None, period_end=None):
    name = subaccount.name

    if period_beg and period_end:
        period_beg = datetime.strptime(period_beg, "%Y-%m-%d")
        period_end = datetime.strptime(period_end, "%Y-%m-%d")
        period_beg = datetime(period_beg.year, period_beg.month, period_beg.day, 0, 0, 0, 0)
        period_end = datetime(period_end.year, period_end.month, period_end.day, 23, 59, 59, 999999)

    if groupby == "All":
        ledgers_query = db.session \
            .query( \
                models.LedgerEntries.ledger, \
                models.LedgerEntries.debit, \
                models.LedgerEntries.credit, \
                models.LedgerEntries.date, \
                models.LedgerEntries.journal_entry_id) \
            .filter_by(currency=currency) \
            .filter_by(ledger=name) \
            .all()

    elif groupby == "Daily":
        ledgers_query = db.session \
            .query( \
                models.LedgerEntries.ledger, \
                func.sum(models.LedgerEntries.debit), \
                func.sum(models.LedgerEntries.credit), \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.date_part('day', models.LedgerEntries.date)) \
            .filter_by(currency=currency) \
            .filter_by(ledger=name) \
            .group_by( \
                models.LedgerEntries.ledger, \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.date_part('day', models.LedgerEntries.date)) \
            .all()

    elif groupby == "Weekly":
        ledgers_query = db.session \
            .query( \
                models.LedgerEntries.ledger, \
                func.sum(models.LedgerEntries.debit), \
                func.sum(models.LedgerEntries.credit), \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('week', models.LedgerEntries.date)) \
            .filter_by(currency=currency) \
            .filter_by(ledger=name) \
            .group_by( \
                models.LedgerEntries.ledger, \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('week', models.LedgerEntries.date)) \
            .all()

    elif groupby == "Monthly":
        ledgers_query = db.session \
            .query( \
                models.LedgerEntries.ledger, \
                func.sum(models.LedgerEntries.debit), \
                func.sum(models.LedgerEntries.credit), \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date)) \
            .filter_by(currency=currency) \
            .filter_by(ledger=name) \
            .group_by( \
                models.LedgerEntries.ledger, \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date)) \
            .all()
    
    subaccount.ledgers = []
    subaccount.totalDebits = 0
    subaccount.totalCredits = 0
    subaccount.debitBalance = 0
    subaccount.creditBalance = 0

    for result in ledgers_query:
        if result[0] == subaccount.name:
            if groupby == "All":
                ledger = result
            elif groupby == "Daily":
                ledger = [result[0], result[1], result[2], datetime(int(result[3]), int(result[4]), int(result[5]), 23, 59, 59, 999999)]
            elif groupby == "Weekly":
                ledger = [result[0], result[1], result[2],  datetime.combine(Week(int(result[3]), int(result[4])).monday(),datetime.min.time())]
            elif groupby == "Monthly":
                lastday = calendar.monthrange(int(result[3]), int(result[4]))[1]
                ledger = [result[0], result[1], result[2], datetime(int(result[3]), int(result[4]), lastday, 23, 59, 59, 999999)]
            if period_beg and period_end:
                if period_beg <= ledger[3] <= period_end:
                    subaccount.ledgers.append(ledger)
            else:
                subaccount.ledgers.append(ledger)
            subaccount.totalDebits += ledger[1]
            subaccount.totalCredits += ledger[2]
    subaccount.ledgers = sorted(subaccount.ledgers, key=itemgetter(3))
    if subaccount.totalDebits > subaccount.totalCredits:
        subaccount.debitBalance = subaccount.totalDebits - subaccount.totalCredits
    elif subaccount.totalDebits < subaccount.totalCredits:
        subaccount.creditBalance = subaccount.totalCredits - subaccount.totalDebits
    return subaccount
예제 #24
0
def trial_balance(currency, groupby, period):
    if groupby == None:
        period = datetime.now()
        lastday = calendar.monthrange(period.year, period.month)[1]
        year = period.year
        month = period.month        
        period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0).strftime('%Y-%m-%d')
        period_end = datetime(period.year, period.month, lastday, 23, 59, 59, 999999).strftime('%Y-%m-%d')
        return redirect(
            url_for('bookkeeping.trial_balance',
            currency='Satoshis',
            groupby='Monthly',
            period='Current'))
            
    if groupby == 'Daily':
        periods = db.session \
            .query(\
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.date_part('day', models.LedgerEntries.date)) \
            .order_by( \
                func.date_part('year', models.LedgerEntries.date).desc(), \
                func.date_part('month', models.LedgerEntries.date).desc(), \
                func.date_part('day', models.LedgerEntries.date).desc()) \
            .group_by( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.date_part('day', models.LedgerEntries.date)) \
            .limit(7)

        periods = sorted([date(int(period[0]), int(period[1]), int(period[2])) for period in periods])
        if period == 'Current':
            period = periods[-1]
        else:
            period = datetime.strptime(period, "%Y-%m-%d")
        
        period_beg = datetime(period.year, period.month, period.day, 0, 0, 0, 0)
        period_end = datetime(period.year, period.month, period.day, 23, 59, 59, 999999)
        
        periods = sorted([period.strftime("%Y-%m-%d") for period in periods])
        period = period.strftime("%Y-%m-%d") 

    elif groupby == 'Weekly':
        periods = db.session \
            .query(\
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('week', models.LedgerEntries.date)) \
            .order_by( \
                func.date_part('year', models.LedgerEntries.date).desc(), \
                func.date_part('week', models.LedgerEntries.date).desc()) \
            .group_by( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('week', models.LedgerEntries.date)) \
            .limit(7)
        periods = sorted([Week(int(period[0]), int(period[1])).monday() for period in periods])
        
        if period == 'Current':
            period = periods[-1]
        else:
            period = period.split('-')
            period = Week(int(period[0]), int(period[1])+1).monday()
            
        period_beg = period - timedelta(days = period.weekday())
        period_end = period_beg + timedelta(days = 6)
        period_beg = datetime(period_beg.year, period_beg.month, period_beg.day, 0, 0, 0, 0)
        period_end = datetime(period_end.year, period_end.month, period_end.day, 23, 59, 59, 999999)
        periods = sorted([period.strftime("%Y-%W") for period in periods])
        period = period.strftime("%Y-%W") 

    elif groupby == 'Monthly':
        if period == 'Current':
            period = datetime.now()
        else:
            period = datetime.strptime(period, "%Y-%m")
        lastday = calendar.monthrange(period.year, period.month)[1]
        period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
        period_end = period = datetime(period.year, period.month, lastday, 23, 59, 59, 999999)

        periods = db.session \
            .query(\
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date)) \
            .group_by( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date)) \
            .all()
        periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
        periods = sorted([period.strftime("%Y-%m") for period in periods])
        period = period.strftime("%Y-%m")
        
    elif groupby == 'Annual':
        if period == 'Current':
            period = datetime.now()
        else:
            period = datetime.strptime(period, "%Y")
        period_beg = datetime(period.year, 1, 1, 0, 0, 0, 0)
        period_end = datetime(period.year, 12, 31, 23, 59, 59, 999999)
        
        periods = db.session \
            .query(func.date_part('year', models.LedgerEntries.date)) \
            .group_by(func.date_part('year', models.LedgerEntries.date)) \
            .all()
        periods = sorted([date(int(period[0]), 12, 31) for period in periods])
        periods = sorted([period.strftime("%Y") for period in periods])
        period = period.strftime("%Y")
    
    subaccounts = db.session \
        .query( \
            models.LedgerEntries.ledger, \
            func.sum(models.LedgerEntries.debit), \
            func.sum(models.LedgerEntries.credit)) \
        .filter(models.LedgerEntries.currency==currency) \
        .filter( models.LedgerEntries.date.between(period_beg, period_end)) \
        .group_by(models.LedgerEntries.ledger) \
        .all()
    totalDebits = 0
    totalCredits = 0
    for subaccount in subaccounts:
            totalDebits += subaccount[1]
            totalCredits += subaccount[2]
    
    return render_template('trial_balance.html',
        groupby=groupby,
        currency=currency,
        periods=periods,
        period=period,
        period_beg=period_beg,
        period_end=period_end,
        totalDebits=totalDebits,
        totalCredits=totalCredits,
        subaccounts=subaccounts)
예제 #25
0
파일: ledgers.py 프로젝트: amachefe/pacioli
def query_entries(accountName, groupby, currency):
    if groupby == "All":
        ledger_entries = models.LedgerEntries \
            .query \
            .filter_by(ledger=accountName) \
            .filter_by(currency=currency) \
            .order_by(models.LedgerEntries.date.desc()) \
            .order_by(models.LedgerEntries.tside.desc()) \
            .all()
        account = foot_account(accountName, ledger_entries, 'All')
    elif groupby == "Daily":
        debit_ledger_entries = db.session\
            .query( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.date_part('day', models.LedgerEntries.date), \
                func.sum(models.LedgerEntries.amount)) \
            .filter_by(ledger=accountName)\
            .filter_by(tside='debit')\
            .filter_by(currency=currency)\
            .group_by( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.date_part('day', models.LedgerEntries.date)) \
            .all()
        credit_ledger_entries = db.session \
            .query( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.date_part('day', models.LedgerEntries.date), \
                func.sum(models.LedgerEntries.amount))\
            .filter_by(currency=currency) \
            .filter_by(ledger=accountName) \
            .filter_by(tside='credit') \
            .group_by( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.date_part('day', models.LedgerEntries.date)) \
            .all()
        ledger_entries = {}
        for entry in debit_ledger_entries:
            day = datetime.date(int(entry[0]), int(entry[1]), int(entry[2]))
            if not day in ledger_entries:
                ledger_entries[day] = {}
            ledger_entries[day]['debit'] = int(entry[3])
        for entry in credit_ledger_entries:
            day = datetime.date(int(entry[0]), int(entry[1]), int(entry[2]))
            if not day in ledger_entries:
                ledger_entries[day] = {}
            ledger_entries[day]['credit'] = int(entry[3])
        ledger_entries = OrderedDict(sorted(ledger_entries.items()))
        account = foot_account(accountName, ledger_entries, 'Summary')
    elif groupby == "Monthly":
        debit_ledger_entries = db.session \
            .query( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.sum(models.LedgerEntries.amount)) \
            .filter_by(ledger=accountName) \
            .filter_by(currency=currency) \
            .filter_by(tside='debit') \
            .group_by( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date)) \
            .all()
        credit_ledger_entries = db.session\
            .query( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date), \
                func.sum(models.LedgerEntries.amount)) \
            .filter_by(ledger=accountName) \
            .filter_by(currency=currency) \
            .filter_by(tside='credit') \
            .group_by( \
                func.date_part('year', models.LedgerEntries.date), \
                func.date_part('month', models.LedgerEntries.date)) \
            .all()
        ledger_entries = {}
        for entry in debit_ledger_entries:
            month = datetime.date(int(entry[0]), int(entry[1]), 1)
            if not month in ledger_entries:
                ledger_entries[month] = {}
            ledger_entries[month]['debit'] = int(entry[2])
        for entry in credit_ledger_entries:
            month = datetime.date(int(entry[0]), int(entry[1]), 1)
            if not month in ledger_entries:
                ledger_entries[month] = {}
            ledger_entries[month]['credit'] = int(entry[2])
        ledger_entries = OrderedDict(sorted(ledger_entries.items()))
        account = foot_account(accountName, ledger_entries, 'Summary')
    return [account, ledger_entries]