コード例 #1
0
 def table_query(cls):
     pool = Pool()
     Month = pool.get('ir.calendar.month')
     month = Month.__table__()
     query = super(SaleOpportunityEmployeeMonthly, cls).table_query()
     opportunity, = query.from_
     type_id = cls.id.sql_type().base
     type_year = cls.year.sql_type().base
     year_column = Extract(
         'YEAR', opportunity.start_date).cast(type_year).as_('year')
     month_index = Extract('MONTH', opportunity.start_date)
     query.from_ = opportunity.join(month,
                                    condition=month_index == month.index)
     query.columns += (
         Max(
             Extract('MONTH', opportunity.start_date) +
             Extract('YEAR', opportunity.start_date) * 100 +
             Coalesce(opportunity.employee, 0) *
             1000000).cast(type_id).as_('id'),
         year_column,
         month.id.as_('month'),
         opportunity.employee,
     )
     query.group_by = (year_column, month.id, opportunity.employee,
                       opportunity.company)
     return query
コード例 #2
0
    def get_lastmodified(cls, uri, cache=None):
        pool = Pool()
        Party = pool.get('party.party')
        Address = pool.get('party.address')
        ContactMechanism = pool.get('party.contact_mechanism')
        party = Party.__table__()
        address = Address.__table__()
        contact_mechanism = ContactMechanism.__table__()

        cursor = Transaction().cursor

        party_id = cls.vcard(uri)
        if party_id:
            if cache is not None:
                cache.setdefault('_contact', {})
                ids = cache['_contact'].keys()
                if party_id not in ids:
                    ids.append(party_id)
                elif 'lastmodified' in cache['_contact'][party_id]:
                    return cache['_contact'][party_id]['lastmodified']
            else:
                ids = [party_id]
            res = None
            for sub_ids in grouped_slice(ids):
                red_sql = reduce_ids(party.id, sub_ids)
                cursor.execute(*party.join(
                    address, 'LEFT', condition=party.id == address.party).join(
                        contact_mechanism,
                        'LEFT',
                        condition=party.id == contact_mechanism.party).select(
                            party.id,
                            Max(
                                Extract(
                                    'EPOCH',
                                    Coalesce(party.write_date,
                                             party.create_date))),
                            Max(
                                Extract(
                                    'EPOCH',
                                    Coalesce(address.write_date,
                                             address.create_date))),
                            Max(
                                Extract(
                                    'EPOCH',
                                    Coalesce(contact_mechanism.write_date,
                                             contact_mechanism.create_date))),
                            where=red_sql,
                            group_by=party.id))
                for party_id2, date_p, date_a, date_c in cursor.fetchall():
                    date = max(date_p, date_a, date_c)
                    if party_id2 == party_id:
                        res = date
                    if cache is not None:
                        cache['_contact'].setdefault(party_id2, {})
                        cache['_contact'][party_id2]['lastmodified'] = date
            if res is not None:
                return res
        return super(Collection, cls).get_lastmodified(uri, cache=cache)
コード例 #3
0
    def table_query(cls):
        pool = Pool()
        Invoice = pool.get('account.invoice')
        InvoiceTax = pool.get('account.invoice.tax')
        Tax = pool.get('account.tax')
        Date = pool.get('ir.date')
        context = Transaction().context
        invoice = Invoice.__table__()
        invoice_tax = InvoiceTax.__table__()
        tax = Tax.__table__()

        amount = invoice_tax.base + invoice_tax.amount
        month = Extract('MONTH', invoice.invoice_date)

        where = ((invoice.company == context.get('company'))
                 & (invoice.state.in_(['posted', 'paid']))
                 & (tax.es_vat_list_code != Null)
                 & (Extract('year', invoice.invoice_date) == context.get(
                     'date', Date.today()).year))
        return (invoice_tax.join(
            invoice, condition=invoice_tax.invoice == invoice.id
        ).join(tax, condition=invoice_tax.tax == tax.id).select(
            Max(invoice_tax.id).as_('id'),
            Literal(0).as_('create_uid'),
            Min(invoice_tax.create_date).as_('create_date'),
            Literal(0).as_('write_uid'),
            Max(invoice_tax.write_date).as_('write_date'),
            invoice.tax_identifier.as_('company_tax_identifier'),
            invoice.party.as_('party'),
            invoice.party_tax_identifier.as_('party_tax_identifier'),
            tax.es_vat_list_code.as_('code'),
            Sum(amount).as_('amount'),
            Sum(amount,
                filter_=month <= Literal(3)).as_('first_period_amount'),
            Sum(amount,
                filter_=((month > Literal(3)) &
                         (month <= Literal(6)))).as_('second_period_amount'),
            Sum(amount,
                filter_=((month > Literal(6)) &
                         (month <= Literal(9)))).as_('third_period_amount'),
            Sum(amount,
                filter_=((month > Literal(9)) &
                         (month <= Literal(12)))).as_('fourth_period_amount'),
            invoice.currency.as_('currency'),
            where=where,
            group_by=[
                invoice.tax_identifier,
                invoice.type,
                invoice.party,
                invoice.party_tax_identifier,
                invoice.currency,
                tax.es_vat_list_code,
            ]))
コード例 #4
0
 def table_query(cls):
     query = super(SaleOpportunityMonthly, cls).table_query()
     opportunity, = query.from_
     type_id = cls.id.sql_type().base
     type_year = cls.year.sql_type().base
     year_column = Extract(
         'YEAR', opportunity.start_date).cast(type_year).as_('year')
     month_column = Extract('MONTH', opportunity.start_date).as_('month')
     query.columns += (Max(
         Extract('MONTH', opportunity.start_date) +
         Extract('YEAR', opportunity.start_date) *
         100).cast(type_id).as_('id'), year_column, month_column)
     query.group_by = (year_column, month_column, opportunity.company)
     return query
コード例 #5
0
 def table_query(cls):
     pool = Pool()
     Month = pool.get('ir.calendar.month')
     month = Month.__table__()
     query = super(SaleOpportunityMonthly, cls).table_query()
     opportunity, = query.from_
     month_timestamp = DateTrunc('MONTH', opportunity.start_date)
     id_ = Extract('EPOCH', month_timestamp)
     month = cls.month.sql_cast(month_timestamp)
     query.columns += (
         id_.as_('id'),
         month.as_('month'),
     )
     query.group_by = [id_, month, opportunity.company]
     return query
コード例 #6
0
    def pull(cls, database, connection, name=None):
        cursor = connection.cursor()
        queue = cls.__table__()

        candidates = With('id', 'scheduled_at', 'expected_at',
            query=queue.select(
                queue.id,
                queue.scheduled_at,
                queue.expected_at,
                where=((queue.name == name) if name else Literal(True))
                & (queue.dequeued_at == Null),
                order_by=[
                    queue.scheduled_at.nulls_first,
                    queue.expected_at.nulls_first]))
        selected = With('id', query=candidates.select(
                candidates.id,
                where=((candidates.scheduled_at <= CurrentTimestamp())
                    | (candidates.scheduled_at == Null))
                & database.lock_id(candidates.id),
                order_by=[
                    candidates.scheduled_at.nulls_first,
                    candidates.expected_at.nulls_first],
                limit=1))
        next_timeout = With('seconds', query=candidates.select(
                Min(Extract('second',
                        candidates.scheduled_at - CurrentTimestamp())
                    ),
                where=candidates.scheduled_at >= CurrentTimestamp()))

        task_id, seconds = None, None
        if database.has_returning():
            query = queue.update([queue.dequeued_at], [CurrentTimestamp()],
                where=queue.id == selected.select(selected.id),
                with_=[candidates, selected, next_timeout],
                returning=[
                    queue.id, next_timeout.select(next_timeout.seconds)])
            cursor.execute(*query)
            row = cursor.fetchone()
            if row:
                task_id, seconds = row
        else:
            query = queue.select(queue.id,
                where=queue.id == selected.select(selected.id),
                with_=[candidates, selected])
            cursor.execute(*query)
            row = cursor.fetchone()
            if row:
                task_id, = row
                query = queue.update([queue.dequeued_at], [CurrentTimestamp()],
                    where=queue.id == task_id)
                cursor.execute(*query)
            query = next_timeout.select(next_timeout.seconds)
            cursor.execute(*query)
            row = cursor.fetchone()
            if row:
                seconds, = row

        if not task_id and database.has_channel():
            cursor.execute('LISTEN "%s"', (cls.__name__,))
        return task_id, seconds
コード例 #7
0
ファイル: webdav.py プロジェクト: bala4901/trytond
 def get_lastmodified(cls, uri, cache=None):
     pool = Pool()
     object_name, object_id = cls._uri2object(uri, cache=cache)
     if object_name == 'ir.attachment':
         Model = pool.get(object_name)
         if object_id:
             if cache is not None:
                 cache.setdefault(Model.__name__, {})
                 ids = cache[Model.__name__].keys()
                 if object_id not in ids:
                     ids.append(object_id)
                 elif 'lastmodified' in cache[Model.__name__][object_id]:
                     return cache[Model.__name__][object_id]['lastmodified']
             else:
                 ids = [object_id]
             res = None
             cursor = Transaction().cursor
             table = Model.__table__()
             for sub_ids in grouped_slice(ids):
                 red_sql = reduce_ids(table.id, sub_ids)
                 cursor.execute(*table.select(
                     table.id,
                     Extract('EPOCH',
                             Coalesce(table.write_date, table.create_date)),
                     where=red_sql))
                 for object_id2, date in cursor.fetchall():
                     if object_id2 == object_id:
                         res = date
                     if cache is not None:
                         cache[Model.__name__].setdefault(object_id2, {})
                         cache[Model.
                               __name__][object_id2]['lastmodified'] = date
             if res is not None:
                 return res
     return time.time()
コード例 #8
0
    def get_creationdate(cls, uri, cache=None):
        Party = Pool().get('party.party')
        party = Party.__table__()
        party_id = cls.vcard(uri)

        cursor = Transaction().connection.cursor()

        if party_id is None:
            raise DAV_NotFound
        if party_id:
            if cache is not None:
                cache.setdefault('_contact', {})
                ids = cache['_contact'].keys()
                if party_id not in ids:
                    ids.append(party_id)
                elif 'creationdate' in cache['_contact'][party_id]:
                    return cache['_contact'][party_id]['creationdate']
            else:
                ids = [party_id]
            res = None
            for sub_ids in grouped_slice(ids):
                red_sql = reduce_ids(party.id, sub_ids)
                cursor.execute(*party.select(party.id,
                        Extract('EPOCH', party.create_date),
                        where=red_sql))
                for party_id2, date in cursor.fetchall():
                    if party_id2 == party_id:
                        res = date
                    if cache is not None:
                        cache['_contact'].setdefault(party_id2, {})
                        cache['_contact'][party_id2]['creationdate'] = date
            if res is not None:
                return res
        return super(Collection, cls).get_creationdate(uri, cache=cache)
コード例 #9
0
 def table_query(cls):
     pool = Pool()
     Line = pool.get('timesheet.line')
     line = Line.__table__()
     year_column = Extract('YEAR', line.date).as_('year')
     week_column = Extract('WEEK', line.date).as_('week')
     return line.select(Max(
         Extract('WEEK', line.date) + Extract('YEAR', line.date) * 100 +
         line.employee * 1000000).as_('id'),
                        Max(line.create_uid).as_('create_uid'),
                        Max(line.create_date).as_('create_date'),
                        Max(line.write_uid).as_('write_uid'),
                        Max(line.write_date).as_('write_date'),
                        year_column,
                        week_column,
                        line.employee,
                        Sum(line.duration).as_('duration'),
                        group_by=(year_column, week_column, line.employee))
コード例 #10
0
 def table_query(cls):
     pool = Pool()
     Line = pool.get('timesheet.line')
     line = Line.__table__()
     type_name = cls.year.sql_type().base
     year_column = Extract('YEAR', line.date).cast(type_name).as_('year')
     month_column = Extract('MONTH', line.date).as_('month')
     return line.select(
         Max(Extract('MONTH', line.date)
             + Extract('YEAR', line.date) * 100
             + line.employee * 1000000).as_('id'),
         Max(line.create_uid).as_('create_uid'),
         Max(line.create_date).as_('create_date'),
         Max(line.write_uid).as_('write_uid'),
         Max(line.write_date).as_('write_date'),
         year_column,
         month_column,
         line.employee,
         Sum(line.duration).as_('duration'),
         group_by=(year_column, month_column, line.employee))
コード例 #11
0
    def __register__(cls, module_name):
        cursor = Transaction().connection.cursor()
        table = cls.__table_handler__(module_name)
        sql_table = cls.__table__()
        pool = Pool()
        Work = pool.get('project.work')
        work = Work.__table__()

        created_progress = not table.column_exist('progress')
        effort_exist = table.column_exist('effort_duration')

        super().__register__(module_name)

        # Migration from 5.0: Effort renamed into to progress
        if created_progress and effort_exist:
            # Don't use UPDATE FROM because SQLite does not support it.
            value = work.select((Extract('EPOCH', sql_table.effort_duration) /
                                 Extract('EPOCH', work.effort_duration)),
                                where=work.id == sql_table.work)
            cursor.execute(*sql_table.update([sql_table.progress], [value]))
コード例 #12
0
 def table_query(cls):
     pool = Pool()
     Line = pool.get('timesheet.line')
     Month = pool.get('ir.calendar.month')
     line = Line.__table__()
     month = Month.__table__()
     year_column = Extract('YEAR', line.date).as_('year')
     month_index = Extract('MONTH', line.date)
     return line.join(month, condition=month_index == month.id).select(
         Max(
             Extract('MONTH', line.date) +
             Extract('YEAR', line.date) * 100 +
             line.employee * 1000000).as_('id'),
         Max(line.create_uid).as_('create_uid'),
         Max(line.create_date).as_('create_date'),
         Max(line.write_uid).as_('write_uid'),
         Max(line.write_date).as_('write_date'),
         year_column,
         month.id.as_('month'),
         line.employee,
         Sum(line.duration).as_('duration'),
         group_by=(year_column, month.id, line.employee))
コード例 #13
0
    def get_lastmodified(cls, uri, cache=None):
        Todo = Pool().get('calendar.todo')
        todo = Todo.__table__()

        transaction = Transaction()
        cursor = transaction.connection.cursor()

        calendar_id = cls.calendar(uri)
        if calendar_id and (uri[10:].split('/', 1) + [None])[1]:
            todo_id = cls.todo(uri, calendar_id=calendar_id)
            if todo_id:
                if cache is not None:
                    cache.setdefault('_calendar', {})
                    cache['_calendar'].setdefault(Todo.__name__, {})
                    ids = cache['_calendar'][Todo.__name__].keys()
                    if todo_id not in ids:
                        ids.append(todo_id)
                    elif 'lastmodified' in cache['_calendar'][
                            Todo.__name__][todo_id]:
                        return cache['_calendar'][
                            Todo.__name__][todo_id]['lastmodified']
                else:
                    ids = [todo_id]
                res = None
                for sub_ids in grouped_slice(ids,
                                             transaction.database.IN_MAX / 2):
                    red_id_sql = reduce_ids(todo.id, sub_ids)
                    red_parent_sql = reduce_ids(todo.parent, sub_ids)
                    cursor.execute(*todo.select(
                        Coalesce(todo.parent, todo.id),
                        Max(
                            Extract(
                                'EPOCH',
                                Coalesce(todo.write_date, todo.create_date))),
                        where=red_id_sql | red_parent_sql,
                        group_by=(todo.parent, todo.id)))
                    for todo_id2, date in cursor.fetchall():
                        if todo_id2 == todo_id:
                            res = date
                        if cache is not None:
                            cache['_calendar'][Todo.__name__]\
                                .setdefault(todo_id2, {})
                            cache['_calendar'][
                                Todo.__name__][todo_id2]['lastmodified'] = date
                if res is not None:
                    return res

        return super(Collection, cls).get_lastmodified(uri, cache=cache)
コード例 #14
0
    def get_creationdate(cls, uri, cache=None):
        Todo = Pool().get('calendar.todo')
        todo = Todo.__table__()

        cursor = Transaction().connection.cursor()

        calendar_id = cls.calendar(uri)
        if not calendar_id:
            calendar_id = cls.calendar(uri, ics=True)
        if calendar_id and (uri[10:].split('/', 1) + [None])[1]:

            todo_id = cls.todo(uri, calendar_id=calendar_id)
            if todo_id:
                if cache is not None:
                    cache.setdefault('_calendar', {})
                    cache['_calendar'].setdefault(Todo.__name__, {})
                    ids = cache['_calendar'][Todo.__name__].keys()
                    if todo_id not in ids:
                        ids.append(todo_id)
                    elif 'creationdate' in cache['_calendar'][
                            Todo.__name__][todo_id]:
                        return cache['_calendar'][
                            Todo.__name__][todo_id]['creationdate']
                else:
                    ids = [todo_id]
                res = None
                for sub_ids in grouped_slice(ids):
                    red_sql = reduce_ids(todo.id, sub_ids)
                    cursor.execute(
                        *todo.select(todo.id,
                                     Extract('EPOCH', todo.create_date),
                                     where=red_sql))
                    for todo_id2, date in cursor.fetchall():
                        if todo_id2 == todo_id:
                            res = date
                        if cache is not None:
                            cache['_calendar'][Todo.__name__]\
                                .setdefault(todo_id2, {})
                            cache['_calendar'][
                                Todo.__name__][todo_id2]['creationdate'] = date
                if res is not None:
                    return res

        return super(Collection, cls).get_creationdate(uri, cache=cache)
コード例 #15
0
ファイル: webdav.py プロジェクト: kakamble-aiims/work
    def get_lastmodified(cls, uri, cache=None):
        pool = Pool()
        Calendar = pool.get('calendar.calendar')
        Event = pool.get('calendar.event')
        calendar = Calendar.__table__()
        event = Event.__table__()

        transaction = Transaction()
        cursor = transaction.connection.cursor()
        calendar_id = cls.calendar(uri)
        if calendar_id:
            if not (uri[10:].split('/', 1) + [None])[1]:
                if cache is not None:
                    cache.setdefault('_calendar', {})
                    cache['_calendar'].setdefault(Calendar.__name__, {})
                    ids = list(cache['_calendar'][Calendar.__name__].keys())
                    if calendar_id not in ids:
                        ids.append(calendar_id)
                    elif 'lastmodified' in cache['_calendar'][
                            Calendar.__name__][calendar_id]:
                        return cache['_calendar'][Calendar.__name__][
                            calendar_id]['lastmodified']
                else:
                    ids = [calendar_id]
                res = None
                for sub_ids in grouped_slice(ids):
                    red_sql = reduce_ids(calendar.id, sub_ids)
                    cursor.execute(*calendar.select(calendar.id,
                            Extract('EPOCH', Coalesce(calendar.write_date,
                                    calendar.create_date)),
                            where=red_sql))
                    for calendar_id2, date in cursor.fetchall():
                        if calendar_id2 == calendar_id:
                            res = date
                        if cache is not None:
                            cache['_calendar'][Calendar.__name__]\
                                .setdefault(calendar_id2, {})
                            cache['_calendar'][Calendar.__name__][
                                calendar_id2]['lastmodified'] = date
                if res is not None:
                    return res
            else:
                event_id = cls.event(uri, calendar_id=calendar_id)
                if event_id:
                    if cache is not None:
                        cache.setdefault('_calendar', {})
                        cache['_calendar'].setdefault(Event.__name__, {})
                        ids = list(cache['_calendar'][Event.__name__].keys())
                        if event_id not in ids:
                            ids.append(event_id)
                        elif 'lastmodified' in cache['_calendar'][
                                Event.__name__][event_id]:
                            return cache['_calendar'][Event.__name__][
                                event_id]['lastmodified']
                    else:
                        ids = [event_id]
                    res = None
                    # for sub_ids in grouped_slice(ids,
                    #        transaction.database.IN_MAX / 2):
                    for sub_ids in grouped_slice(ids):
                        red_id_sql = reduce_ids(event.id, sub_ids)
                        red_parent_sql = reduce_ids(event.parent, sub_ids)
                        cursor.execute(*event.select(
                                Coalesce(event.parent, event.id),
                                Max(Extract('EPOCH', Coalesce(event.write_date,
                                            event.create_date))),
                                where=red_id_sql | red_parent_sql,
                                group_by=(event.parent, event.id)))
                        for event_id2, date in cursor.fetchall():
                            if event_id2 == event_id:
                                res = date
                            if cache is not None:
                                cache['_calendar'][Event.__name__]\
                                    .setdefault(event_id2, {})
                                cache['_calendar'][Event.__name__][
                                    event_id2]['lastmodified'] = date
                    if res is not None:
                        return res
        calendar_ics_id = cls.calendar(uri, ics=True)
        if calendar_ics_id:
            if cache is not None:
                cache.setdefault('_calendar', {})
                cache['_calendar'].setdefault(Calendar.__name__, {})
                ids = list(cache['_calendar'][Calendar.__name__].keys())
                if calendar_ics_id not in ids:
                    ids.append(calendar_ics_id)
                elif 'lastmodified ics' in cache['_calendar'][
                        Calendar.__name__][calendar_ics_id]:
                    return cache['_calendar'][Calendar.__name__][
                        calendar_ics_id]['lastmodified ics']
            else:
                ids = [calendar_ics_id]
            res = None
            for sub_ids in grouped_slice(ids):
                red_sql = reduce_ids(event.calendar, sub_ids)
                cursor.execute(*event.select(event.calendar,
                        Max(Extract('EPOCH', Coalesce(event.write_date,
                                    event.create_date))),
                        where=red_sql,
                        group_by=event.calendar))
                for calendar_id2, date in cursor.fetchall():
                    if calendar_id2 == calendar_ics_id:
                        res = date
                    if cache is not None:
                        cache['_calendar'][Calendar.__name__]\
                            .setdefault(calendar_id2, {})
                        cache['_calendar'][Calendar.__name__][
                            calendar_id2]['lastmodified ics'] = date
            if res is not None:
                return res
        return super(Collection, cls).get_lastmodified(uri, cache=cache)
コード例 #16
0
ファイル: webdav.py プロジェクト: kakamble-aiims/work
    def get_creationdate(cls, uri, cache=None):
        Calendar = Pool().get('calendar.calendar')
        Event = Pool().get('calendar.event')
        calendar = Calendar.__table__()
        event = Event.__table__()

        calendar_id = cls.calendar(uri)
        if not calendar_id:
            calendar_id = cls.calendar(uri, ics=True)
        if calendar_id:
            if not (uri[10:].split('/', 1) + [None])[1]:
                if cache is not None:
                    cache.setdefault('_calendar', {})
                    cache['_calendar'].setdefault(Calendar.__name__, {})
                    ids = list(cache['_calendar'][Calendar.__name__].keys())
                    if calendar_id not in ids:
                        ids.append(calendar_id)
                    elif 'creationdate' in cache['_calendar'][
                            Calendar.__name__][calendar_id]:
                        return cache['_calendar'][Calendar.__name__][
                            calendar_id]['creationdate']
                else:
                    ids = [calendar_id]
                res = None
                cursor = Transaction().connection.cursor()
                for sub_ids in grouped_slice(ids):
                    red_sql = reduce_ids(calendar.id, sub_ids)
                    cursor.execute(*calendar.select(calendar.id,
                            Extract('EPOCH', calendar.create_date),
                            where=red_sql))
                    for calendar_id2, date in cursor.fetchall():
                        if calendar_id2 == calendar_id:
                            res = date
                        if cache is not None:
                            cache['_calendar'][Calendar.__name__]\
                                .setdefault(calendar_id2, {})
                            cache['_calendar'][Calendar.__name__][
                                calendar_id2]['creationdate'] = date
                if res is not None:
                    return res
            else:
                event_id = cls.event(uri, calendar_id=calendar_id)
                if event_id:
                    if cache is not None:
                        cache.setdefault('_calendar', {})
                        cache['_calendar'].setdefault(Event.__name__, {})
                        ids = list(cache['_calendar'][Event.__name__].keys())
                        if event_id not in ids:
                            ids.append(event_id)
                        elif 'creationdate' in cache['_calendar'][
                                Event.__name__][event_id]:
                            return cache['_calendar'][Event.__name__][
                                event_id]['creationdate']
                    else:
                        ids = [event_id]
                    res = None
                    cursor = Transaction().connection.cursor()
                    for sub_ids in grouped_slice(ids):
                        red_sql = reduce_ids(event.id, sub_ids)
                        cursor.execute(*event.select(event.id,
                                Extract('EPOCH', event.create_date),
                                where=red_sql))
                        for event_id2, date in cursor.fetchall():
                            if event_id2 == event_id:
                                res = date
                            if cache is not None:
                                cache['_calendar'][Event.__name__]\
                                    .setdefault(event_id2, {})
                                cache['_calendar'][Event.__name__][
                                    event_id2]['creationdate'] = date
                    if res is not None:
                        return res
        return super(Collection, cls).get_creationdate(uri, cache=cache)
コード例 #17
0
    def table_query(cls):
        pool = Pool()
        Company = pool.get('company.company')
        Invoice = pool.get('account.invoice')
        InvoiceTax = pool.get('account.invoice.tax')
        Move = pool.get('account.move')
        Line = pool.get('account.move.line')
        TaxLine = pool.get('account.tax.line')
        Tax = pool.get('account.tax')
        TaxCode = pool.get('account.tax.code')
        TaxCodeLine = pool.get('account.tax.code.line')
        Date = pool.get('ir.date')
        context = Transaction().context
        company = Company.__table__()
        invoice = Invoice.__table__()
        cancel_invoice = Invoice.__table__()
        move = Move.__table__()
        cancel_move = Move.__table__()
        line = Line.__table__()
        tax_line = TaxLine.__table__()
        tax = Tax.__table__()
        tax_code = TaxCode.__table__()
        tax_code_line = TaxCodeLine.__table__()
        exclude_invoice_tax = InvoiceTax.__table__()

        amount = tax_line.amount
        month = Extract('MONTH', invoice.invoice_date)

        excluded_taxes = (tax_code_line
            .join(tax_code,
                condition=(tax_code.id == tax_code_line.code)
                ).select(
                    tax_code_line.tax, distinct=True,
                    where=tax_code.aeat_report.in_(cls.excluded_tax_codes())))

        where = ((invoice.company == context.get('company'))
            & (tax.es_vat_list_code != Null)
            & (Extract('year', invoice.invoice_date)
                == context.get('date', Date.today()).year)
            # Exclude base amount for es_reported_with taxes because it is
            # already included in the base of main tax
            & ((tax.es_reported_with == Null) | (tax_line.type == 'tax'))
            & ~Exists(cancel_invoice
                .join(cancel_move,
                    condition=cancel_invoice.cancel_move == cancel_move.id)
                .select(cancel_invoice.id, distinct=True,
                     where=((cancel_invoice.id == invoice.id)
                         & (~cancel_move.origin.like('account.invoice,%')))))
            # Use exists to exclude the full invoice when it has multiple taxes
            & ~Exists(exclude_invoice_tax.select(
                    exclude_invoice_tax.invoice,
                    where=((exclude_invoice_tax.invoice == invoice.id)
                        & (exclude_invoice_tax.tax.in_(excluded_taxes))))))
        return (tax_line
            .join(tax, condition=tax_line.tax == tax.id)
            .join(line, condition=tax_line.move_line == line.id)
            .join(move, condition=line.move == move.id)
            .join(invoice, condition=invoice.move == move.id)
            .join(company, condition=company.id == invoice.company)
            .select(
                Min(tax_line.id).as_('id'),
                Literal(0).as_('create_uid'),
                CurrentTimestamp().as_('create_date'),
                cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'),
                cls.write_date.sql_cast(Literal(Null)).as_('write_date'),
                invoice.tax_identifier.as_('company_tax_identifier'),
                invoice.party.as_('party'),
                invoice.party_tax_identifier.as_('party_tax_identifier'),
                tax.es_vat_list_code.as_('code'),
                Sum(amount).as_('amount'),
                Sum(amount, filter_=month <= Literal(3)).as_(
                    'first_period_amount'),
                Sum(amount, filter_=(
                        (month > Literal(3)) & (month <= Literal(6)))).as_(
                    'second_period_amount'),
                Sum(amount, filter_=(
                        (month > Literal(6)) & (month <= Literal(9)))).as_(
                    'third_period_amount'),
                Sum(amount, filter_=(
                        (month > Literal(9)) & (month <= Literal(12)))).as_(
                    'fourth_period_amount'),
                company.currency.as_('currency'),
                where=where,
                group_by=[
                    invoice.tax_identifier,
                    invoice.type,
                    invoice.party,
                    invoice.party_tax_identifier,
                    company.currency,
                    tax.es_vat_list_code,
                    ]))