Evaluation based on 3 fields party, work and month

[mimo_Alva (mimoms)]

Hi,

I need to add another behaviour in the timesheet module where i want to do evaluation by " party, by work and month" , i have developed this code but i still facing some issue and this negative point that it just show 1 record if we have 1 work done for many parties fr example if we have work 1 Done for party Maria, Diego, Alice(3 lines), the negative behaviour here that it just display 1 record for example it shows just record of party “Diego”, here is my code :

class HoursWorkMonthly(ModelSQL, ModelView):
    'Hours per Work per Month'
    __name__ = 'timesheet.hours_work_monthly'
    year = fields.Char('Year')
    month = fields.Many2One('ir.calendar.month', "Month")
    party = fields.Many2One('party.party', 'Party', required=True)
    duration = fields.TimeDelta('duration', 'company_work_time')
    work = fields.Many2One('timesheet.work', 'work')

    @classmethod
    def __setup__(cls):
        super(HoursWorkMonthly, cls).__setup__()
        cls._order.insert(0, ('year', 'DESC'))
        cls._order.insert(1, ('month.index', 'DESC'))
        cls._order.insert(2, ('party', 'ASC'))
        
    @classmethod
    def table_query(cls):
        pool = Pool()
        Line = pool.get('timesheet.line')
        Month = pool.get('ir.calendar.month')
        line = Line.__table__()
        month = Month.__table__()
        type_name = cls.year.sql_type().base
        year_column = Extract('YEAR', line.date).cast(type_name).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.work * 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.work,
            line.party, 
            Sum(line.duration).as_('duration'),
            group_by=(year_column, month.id, line.work, line.party)
    )

for now this code display if paryt1 do work1 in duration 2h in september and party1 do work1 in duration 3h in september it will show me just 1 record say party1 do work1 duration 5h ins eptember and that what i need but also i need another behaviour that it must show me all the records(all the lines i mean) for 1 work if this work done for many parties for example we have work 1 Done for party Maria, Diego, Alice, I want to show me all the lines and not just 1 record( 1line)
any help will be appreciated, thanks.

[ced (Cédric Krier)]

You must make the id unique per year, month, work and party.
This is not easy when you have 2 records but we have sql_pairing which can be used.

Now I’m wondering if we could not have something like that in standard but instead of having a main report, I think it could be an extension of the existing reports “Hours per Works” and “Hours per Employee”.

[mimo_Alva (mimoms)]

Hi, Mr@ced, thanks for your response, Now i have updated the code like this;:

    @classmethod
    def table_query(cls):
        pool = Pool()
        Line = pool.get('timesheet.line')
        Month = pool.get('ir.calendar.month')
        line = Line.__table__()
        month = Month.__table__()

        # Extract year and month from the date field in timesheet.line
        type_name = cls.year.sql_type().base
        year_column = Extract('YEAR', line.date).cast(type_name).as_('year')
        month_index = Extract('MONTH', line.date)

        # Join lines with the month table and aggregate based on party, work, month, and year
        return line.join(month, condition=month_index == month.id).select(
            Max(Extract('MONTH', line.date)
                + Extract('YEAR', line.date) * 100
                + line.party * 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.party.as_('party'),
            line.work.as_('work'),
            Sum(line.duration).as_('duration'),
            group_by=(year_column, month.id, line.party, line.work)
        )

but there were still another new behaviour happen now that if we have “party1, month, year” related to work1 and work2 it display just 1line , I mean it display party1, work1, month,year with ignoring off party1, work2, month, year
and me i want it to display the 2 lines party1, work1, month, year and party1, work2, month, year

[ced (Cédric Krier)]

Of course it does not work. You replaced the work by the party.
I explained that that the id column must be unique so it should be a combination of both. This can be achieved by using sql_pairing.

[mimo_Alva (mimoms)]

thanks Mr@ced, here is the solution that i have implement , here is the code (may be will be helpful for someone )

    def table_query(cls):
        pool = Pool()
        Line = pool.get('timesheet.line')
        Month = pool.get('ir.calendar.month')
        line = Line.__table__()
        month = Month.__table__()

        # Extract year and month from the date field in timesheet.line
        type_name = cls.year.sql_type().base
        year_column = Extract('YEAR', line.date).cast(type_name).as_('year')
        month_index = Extract('MONTH', line.date)

        # Join lines with the month table and aggregate based on party, work, month, and year
        return line.join(month, condition=month_index == month.id).select(
            Max(Extract('MONTH', line.date)
                + Extract('YEAR', line.date) * 100
                + line.party * 1000000
                + line.work * 100000000).as_('id'),  # Ensure id is unique for each combination
            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.party.as_('party'),
            line.work.as_('work'),
            Sum(line.duration).as_('duration'),
            group_by=(year_column, month.id, line.party, line.work)
        )