Slink/sos_sales/report/spenco_report.py

from odoo import models, api
from datetime import datetime, timedelta

class SpencoReport(models.AbstractModel):
    _name = 'report.sos_sales.report_spenco'
    _description = 'Spenco Report'

    @api.model
    def _get_report_values(self, docids, data=None):
        sales_person_id = data.get('sales_person_id')
        from_date = data.get('from_date')
        to_date = data.get('to_date')
        customer_id = data.get('customer_id')
        # Build domain for querying records
        domain = [('status', '!=', 'close')]
        domain.append(('spenco_status', 'not in', ('Suspects', '', False, None)))
        if customer_id:
            domain.append(('customer_name', '=', customer_id))
        if sales_person_id:
            domain.append(('sales_person', '=', sales_person_id))
        if from_date and isinstance(from_date, str):
            from_date = datetime.strptime(from_date, "%Y-%m-%d").date()
        if to_date and isinstance(to_date, str):
            to_date = datetime.strptime(to_date, "%Y-%m-%d").date()

        if from_date and to_date:
            next_day = to_date + timedelta(days=1)
            matching_lines = self.env['sos_case_diary_line'].search([
                ('status_changed_on', '>=', from_date),
                ('status_changed_on', '<', next_day),
                ('current_state_value', '>', 0.0),
            ])
            domain.append(('line_ids', 'in', matching_lines.ids))

        elif from_date:
            matching_lines = self.env['sos_case_diary_line'].search([
                ('status_changed_on', '>=', from_date),
                ('current_state_value', '>', 0.0),
            ])
            domain.append(('line_ids', 'in', matching_lines.ids))

        elif to_date:
            next_day = to_date + timedelta(days=1)
            matching_lines = self.env['sos_case_diary_line'].search([
                ('status_changed_on', '<', next_day),
                ('current_state_value', '>', 0.0),
            ])
            domain.append(('line_ids', 'in', matching_lines.ids))
        # Fetch records
        records = self.env['sos_case_diary'].search(domain)
        case_diary_data = []
        totals = {
            'Prospects': 0,
            'Engaged': 0,
            'Negotiation': 0,
            'Commercial Order': 0,
            'Expected Order Value': 0,
        }
        if not records:
            return {
                'doc_ids': docids,
                'doc_model': 'sos_case_diary',
                'docs': records,
                'case_diary_data': [],
                'from_date': from_date,
                'to_date': to_date,
                'totals': totals,  # Include totals to avoid template errors
            }
        # Process records to build case_diary_data
        for record in records:
            if record.interested_in == "projects":
                product_or_project = record.project_name
            else:
                product_or_project = record.products
            latest_record = self.env['sos_case_diary_line'].search(
                [('ref_id', '=', record.id)],
                order="id desc",
                limit=1
            )
            if not latest_record:
                continue  # Skip if no latest record found

            current_state_value = round(latest_record.current_state_value, 2)
            spenco_status = latest_record.spenco_status
            
            if spenco_status == 'Prospects':
                totals['Prospects'] += current_state_value
                totals['Expected Order Value'] += round(current_state_value * 0.1, 2)
            elif spenco_status == 'Engaged':
                totals['Engaged'] += current_state_value
                totals['Expected Order Value'] += round(current_state_value * 0.3, 2)
            elif spenco_status == 'Negotiation':
                totals['Negotiation'] += current_state_value
                totals['Expected Order Value'] += round(current_state_value * 0.5, 2)
            elif spenco_status == 'Commercial Order':
                totals['Commercial Order'] += current_state_value
                totals['Expected Order Value'] += round(current_state_value, 2)
            
            case_diary_data.append({
                'record_id': record.id,
                'quote_no':record.quote_no,
                'customer_name': record.customer_name.customer_name,
                'end_customer_name': record.end_customer_name,
                'customer_city': record.customer_city,
                'lead_generated_by': record.lead_generated_by,
                'products': product_or_project,
                'quantity': record.quantity,
                'sales_person': record.sales_person.name,
                'account_start_date': record.account_start_date,
                'status_changed_on': latest_record.status_changed_on,
                'spenco_status': latest_record.spenco_status,
                'current_state_value': latest_record.current_state_value,
                'status_values': {
                    'Prospects': latest_record.current_state_value if latest_record.spenco_status == 'Prospects' else '',
                    'Engaged': latest_record.current_state_value if latest_record.spenco_status == 'Engaged' else '',
                    'Negotiation': latest_record.current_state_value if latest_record.spenco_status == 'Negotiation' else '',
                    'Commercial Order': latest_record.current_state_value if latest_record.spenco_status == 'Commercial Order' else '',
                },
                'expected_order_value': round(
                    latest_record.current_state_value * (
                        0.1 if latest_record.spenco_status == 'Prospects' else
                        0.3 if latest_record.spenco_status == 'Engaged' else
                        0.5 if latest_record.spenco_status == 'Negotiation' else
                        1.0 if latest_record.spenco_status == 'Commercial Order' else 0
                    ), 2
                ),
                'totals': totals,
                'from_date': from_date,
                'to_date': to_date
            })

        # Sort by sales_person
        case_diary_data = sorted(case_diary_data, key=lambda x: x['sales_person'])

        # Return values for the report template
        return {
            'doc_ids': docids,
            'doc_model': 'sos_case_diary',
            'docs': records,
            'case_diary_data': case_diary_data,
            'from_date': from_date,
            'to_date': to_date,
            'sales_person':self.env['res.users'].browse(sales_person_id)
        }