path: root/indoteknik_custom/models/purchase_order.py

from odoo import fields, models, api, _
from odoo.exceptions import AccessError, UserError, ValidationError
from dateutil.relativedelta import relativedelta
from datetime import datetime, timedelta
import logging
from pytz import timezone, utc
import io
import base64
try:
    from odoo.tools.misc import xlsxwriter
except ImportError:
    import xlsxwriter

_logger = logging.getLogger(__name__)

            
class PurchaseOrder(models.Model):
    _inherit = 'purchase.order'

    order_sales_match_line = fields.One2many('purchase.order.sales.match', 'purchase_order_id', string='Sales Match Lines', states={'cancel': [('readonly', True)], 'done': [('readonly', True)]}, copy=True)
    sale_order_id = fields.Many2one('sale.order', string='Sale Order')
    procurement_status = fields.Char(string='Procurement Status', compute='get_procurement_status', readonly=True)
    po_status = fields.Selection([
        ('terproses', 'Terproses'),
        ('sebagian', 'Sebagian Diproses'),
        ('menunggu', 'Menunggu Diproses'),
    ])
    approval_status = fields.Selection([
        ('pengajuan1', 'Approval Manager'), #siapa? darren - 11
        ('pengajuan2', 'Approval Pimpinan'), #akbar - 7 temporary not used
        ('approved', 'Approved'),
    ], string='Approval Status', readonly=True, copy=False, index=True, tracking=3)
    delivery_amount = fields.Float('Delivery Amount', compute='compute_delivery_amount')
    delivery_amt = fields.Float('Delivery Amt')
    total_margin = fields.Float(
        'Margin', compute='compute_total_margin',
        help="Total Margin in Sales Order Header")
    total_percent_margin = fields.Float(
        'Margin%', compute='compute_total_margin',
        help="Total % Margin in Sales Order Header")
    total_so_margin = fields.Float(
        'SO Margin', compute='compute_total_margin',
        help="Total Margin in Sales Order Header")
    total_so_percent_margin = fields.Float(
        'SO Margin%', compute='compute_total_margin',
        help="Total % Margin in Sales Order Header")
    amount_total_without_service = fields.Float('AmtTotalWithoutService', compute='compute_amt_total_without_service')
    summary_qty_po = fields.Float('Total Qty', compute='_compute_summary_qty')
    summary_qty_receipt = fields.Float('Summary Qty Receipt', compute='_compute_summary_qty')
    count_line_product = fields.Float('Total Item', compute='compute_count_line_product')
    note_description = fields.Char(string='Noteman', help='bisa diisi sebagai informasi indent barang tertentu atau apapun')
    has_active_invoice = fields.Boolean(string='Has Active Invoice', compute='_compute_has_active_invoice')
    description = fields.Char(string='Description', help='bisa diisi sebagai informasi indent barang tertentu atau apapun')
    purchase_order_lines = fields.One2many('purchase.order.line', 'order_id', string='Indent', auto_join=True)
    responsible_ids = fields.Many2many('res.users', string='Responsibles', compute='_compute_responsibles')
    status_paid_cbd = fields.Boolean(string='Paid Status', tracking=3, help='Field ini diisi secara manual oleh Finance AP dan hanya untuk status PO CBD')
    revisi_po = fields.Boolean(string='Revisi', tracking=3)
    from_apo = fields.Boolean(string='From APO', tracking=3)
    approval_edit_line = fields.Boolean(string='Approval Edit Line', tracking=3)
    sale_order = fields.Char(string='Sale Order')
    matches_so = fields.Many2many('sale.order', string='Matches SO', compute='_compute_matches_so')
    is_create_uangmuka = fields.Boolean(string='Uang Muka?')
    move_id = fields.Many2one('account.move', string='Account Move')
    logbook_bill_id = fields.Many2one('report.logbook.bill', string='Logbook Bill')
    status_printed = fields.Selection([
        ('not_printed', 'Belum Print'),
        ('printed', 'Printed')
    ], string='Printed?', copy=False, tracking=True)
    date_done_picking = fields.Datetime(string='Date Done Picking', compute='get_date_done')
    bills_dp_id = fields.Many2one('account.move', string='Bills DP')
    bills_pelunasan_id = fields.Many2one('account.move', string='Bills Pelunasan')
    grand_total = fields.Monetary(string='Grand Total', help='Amount total + amount delivery', compute='_compute_grand_total')
    total_margin_match = fields.Float(string='Total Margin Match', compute='_compute_total_margin_match')
    approve_by = fields.Many2one('res.users', string='Approve By')
    exclude_incoming = fields.Boolean(string='Exclude Incoming', default=False,
                                      help='Centang jika tidak mau masuk perhitungan Incoming Qty')

    def _compute_total_margin_match(self):
        for purchase in self:
            match = self.env['purchase.order.sales.match']
            result = match.read_group(
                [('purchase_order_id', '=', purchase.id)],
                ['margin_item'],
                []
            )
            purchase.total_margin_match = result[0].get('margin_item', 0.0)
    
    def _compute_grand_total(self):
        for order in self:
            if order.delivery_amt:
                order.grand_total = order.delivery_amt + order.amount_total
            else:
                order.grand_total = order.amount_total

    def create_bill_pelunasan(self):
        if not self.env.user.is_accounting:
            raise UserError('Hanya Accounting yang bisa bikin bill dp')

        # Check for existing vendor bills with the same reference and partner
        existing_bill = self.env['account.move'].search([
            ('ref', '=', self.name),
            ('partner_id', '=', self.partner_id.id),
            ('move_type', '=', 'in_invoice'),
            ('state', 'not in', ['cancel', 'posted'])
        ], limit=1)

        if existing_bill:
            raise UserError(_('Duplicated vendor reference detected. You probably encoded twice the same vendor bill/credit note: %s') % existing_bill.name)

        current_date = datetime.utcnow()
        data_bills = {
            'partner_id': self.partner_id.id,
            'partner_shipping_id': self.partner_id.id,
            'ref': self.name,
            'invoice_date': current_date,
            'date': current_date,
            'move_type': 'in_invoice'
        }

        bills = self.env['account.move'].create([data_bills])

        product_dp = self.env['product.product'].browse(229625)

        data_line_bills = []

        move_line = self.env['account.move.line'].search([
            ('move_id', '=', self.bills_dp_id.id),
            ('product_id', '=', product_dp.id),
        ])

        bills.message_post(
            body=f"<div>"
                f"<b>DP :</b><br>{move_line.price_unit}</div>",
            subtype_id=self.env.ref("mail.mt_note").id
        )

        data_line_bills.append({
            'move_id': bills.id,
            'product_id': product_dp.id,  # product down payment
            'name': '[IT.121456] Down Payment',  # product down payment
            'account_id': 401,  # Uang Muka persediaan barang dagang
            # 'price_unit': move_line.price_unit,
            'quantity': -1,
            'product_uom_id': 1,
            'tax_ids': [(5, 0, 0)] + [(4, tax.id) for tax in product_dp.taxes_id],
        })

        for line in self.order_line:
            if line.product_id:
                data_line_bills.append({
                    'move_id': bills.id,
                    'product_id': line.product_id.id,
                    'name': self.name + ": " + line.product_id.display_name,
                    'account_id': 439,  # Uang Muka persediaan barang dagang
                    'quantity': line.product_qty,
                    # 'price_unit': line.price_subtotal,
                    'product_uom_id': line.product_uom.id,
                    'tax_ids': [(5, 0, 0)] + [(4, tax.id) for tax in line.taxes_id],
                    'purchase_line_id': line.id,
                    'purchase_order_id': line[0].order_id.id,
                })

        bills_line = self.env['account.move.line'].create(data_line_bills)

        self.bills_pelunasan_id = bills.id

        return {
            'name': _('Account Move'),
            'view_mode': 'tree,form',
            'res_model': 'account.move',
            'target': 'current',
            'type': 'ir.actions.act_window',
            'domain': [('id', '=', bills.id)]
        }


    
    def create_bill_dp(self):
        if not self.env.user.is_accounting:
            raise UserError('Hanya Accounting yang bisa bikin bill dp')
        
        current_date = datetime.utcnow()
        data_bills = {
            'partner_id': self.partner_id.id,
            'partner_shipping_id': self.partner_id.id,
            'ref': self.name,
            'invoice_date': current_date,
            'date': current_date,
            'move_type': 'in_invoice'

        }

        bills = self.env['account.move'].create([data_bills])

        product_dp = self.env['product.product'].browse(229625)

        data_line_bills = {
            'move_id': bills.id,
            'product_id': product_dp.id, #product down payment
            'account_id': 401, #Uang Muka persediaan barang dagang
            'quantity': 1,
            'product_uom_id': 1,
            'tax_ids': [line[0].taxes_id.id for line in self.order_line],
        }


        bills_line = self.env['account.move.line'].create([data_line_bills])

        self.bills_dp_id = bills.id

        move_line = bills.line_ids
        move_line.name = '[IT.121456] Down Payment'
        move_line.partner_id = self.partner_id.id

        return {
            'name': _('Account Move'),
            'view_mode': 'tree,form',
            'res_model': 'account.move',
            'target': 'current',
            'type': 'ir.actions.act_window',
            'domain': [('id', '=', bills.id)]
        }

    def get_date_done(self):
        picking = self.env['stock.picking'].search([
            ('purchase_id', '=', self.id),
            ('state', '=', 'done')
        ], limit=1, order='create_date desc')
        
        self.date_done_picking = picking.date_done
    def _prepare_invoice(self):
        """Prepare the dict of values to create the new invoice for a purchase order.
        """
        self.ensure_one()
        move_type = self._context.get('default_move_type', 'in_invoice')
        journal = self.env['account.move'].with_context(default_move_type=move_type)._get_default_journal()
        if not journal:
            raise UserError(_('Please define an accounting purchase journal for the company %s (%s).') % (self.company_id.name, self.company_id.id))

        date_done = self.date_approve

        day_extension = int(self.payment_term_id.line_ids.days)
        payment_schedule = date_done + timedelta(days=day_extension)
                
        if payment_schedule.weekday() == 0:  
            payment_schedule -= timedelta(days=4)  
        elif payment_schedule.weekday() == 2:  
            payment_schedule -= timedelta(days=1)  
        elif payment_schedule.weekday() == 4:  
            payment_schedule -= timedelta(days=1)
        elif payment_schedule.weekday() == 5:  
            payment_schedule -= timedelta(days=2)
        elif payment_schedule.weekday() == 6:  
            payment_schedule -= timedelta(days=3)

        partner_invoice_id = self.partner_id.address_get(['invoice'])['invoice']
        invoice_vals = {
            'ref': self.partner_ref or '',
            'move_type': move_type,
            'narration': self.notes,
            'currency_id': self.currency_id.id,
            'invoice_user_id': self.user_id and self.user_id.id or self.env.user.id,
            'partner_id': partner_invoice_id,
            'fiscal_position_id': (self.fiscal_position_id or self.fiscal_position_id.get_fiscal_position(partner_invoice_id)).id,
            'payment_reference': self.partner_ref or '',
            'partner_bank_id': self.partner_id.bank_ids[:1].id,
            'invoice_origin': self.name,
            'invoice_payment_term_id': self.payment_term_id.id,
            'invoice_line_ids': [],
            'company_id': self.company_id.id,
            'payment_schedule': payment_schedule
        }
        return invoice_vals

    def _compute_matches_so(self):
        for po in self:
            matches = []
            for match in po.order_sales_match_line:
                matches.append(match.sale_id.id)
            matches = list(set(matches))
            po.matches_so = matches

    def _prepare_picking(self):
        if not self.group_id:
            self.group_id = self.group_id.create({
                'name': self.name,
                'partner_id': self.partner_id.id
            })
        
        if self.sale_order_id:
            sale_order = self.sale_order_id
        else:
            sale_order = self.sale_order

        if not self.partner_id.property_stock_supplier.id:
            raise UserError(_("You must set a Vendor Location for this partner %s", self.partner_id.name))
        return {
            'picking_type_id': self.picking_type_id.id,
            'partner_id': self.partner_id.id,
            'user_id': False,
            'date': self.date_order,
            'origin': self.name,
            'location_dest_id': self._get_destination_location(),
            'location_id': self.partner_id.property_stock_supplier.id,
            'company_id': self.company_id.id,
            'sale_order': sale_order
        }

    @api.model
    def action_multi_cancel(self):
        for purchase in self:
            purchase.update({
                'state': 'cancel',
            })

            if purchase.state == 'cancel':
                purchase.update({
                    'approval_status': False,
                })

    def open_form_multi_cancel(self):
        action = self.env['ir.actions.act_window']._for_xml_id('indoteknik_custom.action_po_multi_cancel')
        action['context'] = {
            'purchase_ids': [x.id for x in self]
        }
        return action

    def delete_line(self):
        lines_to_delete = self.order_line.filtered(lambda line: line.suggest == 'masih cukup')
        if not lines_to_delete:
            raise UserError('Tidak ada item yang masih cukup')
        lines_to_delete.unlink()

    def open_form_multi_confirm_po(self):
        action = self.env['ir.actions.act_window']._for_xml_id('indoteknik_custom.action_purchase_order_multi_confirm')
        action['context'] = {
            'order_ids': [x.id for x in self]
        }
        return action

    def open_form_multi_create_uang_muka(self):
        action = self.env['ir.actions.act_window']._for_xml_id('indoteknik_custom.action_purchase_order_multi_uangmuka')
        action['context'] = {
            'po_ids': [x.id for x in self]
        }
        return action
    
    def open_form_multi_create_uang_muka2(self):
        action = self.env['ir.actions.act_window']._for_xml_id('indoteknik_custom.action_purchase_order_multi_uangmuka')
        action['context'] = {
            'po_ids': self.id
        }
        return action

    def action_multi_update_paid_status(self):
        for purchase in self:
            purchase.update({
                'status_paid_cbd': True,
            })

    def action_multi_confirm_po(self):
        for purchase in self:
            if purchase.state != 'draft':
                continue
            
            purchase.button_confirm()

    def open_form_multi_update_paid_status(self):
        action = self.env['ir.actions.act_window']._for_xml_id('indoteknik_custom.action_purchase_order_multi_update')
        action['context'] = {
            'purchase_ids': [x.id for x in self]
        }
        return action

    def _compute_responsibles(self):
        for purchase in self:
            resposible_ids = []
            for line in purchase.order_line:
                resposible_ids.append(line.product_id.x_manufacture.user_id.id)
            resposible_ids = list(set(resposible_ids))
            purchase.responsible_ids = resposible_ids

    def _compute_has_active_invoice(self):
        for order in self:
            order.has_active_invoice = any(invoice.state != 'cancel' for invoice in order.invoice_ids)

    def add_product_to_pricelist(self):
        i = 0
        for line in self.order_line:
            i += 1
            current_time = datetime.utcnow()
            # print(i, len(self.order_line))

            price_unit = line.price_unit
            taxes = line.taxes_id
            # for tax in taxes:
            #     tax_include = tax.price_include
            # if taxes:
            #     if tax_include:
            #         price_unit = price_unit
            #     else:
            #         price_unit = price_unit + (price_unit * 11 / 100)
            # else:
            #     price_unit = price_unit + (price_unit * 11 / 100)

            purchase_pricelist = self.env['purchase.pricelist'].search([
                ('product_id', '=', line.product_id.id),
                ('vendor_id', '=', line.order_id.partner_id.id)
                ])
            purchase_pricelist = purchase_pricelist.with_context(update_by='system')
            if not purchase_pricelist:
                purchase_pricelist.create([{
                    'vendor_id': line.order_id.partner_id.id,
                    'product_id': line.product_id.id,
                    'product_price': 0,
                    'taxes_system_id': taxes.id,
                    'system_price': price_unit,
                    'system_last_update': current_time
                }])
            else:
                purchase_pricelist.write({
                    'system_last_update': current_time,
                    'taxes_system_id': taxes.id,
                    'system_price': price_unit
                })

    def _compute_date_planned(self):
        for order in self:
            if order.date_approve:
                leadtime = order.partner_id.leadtime
                current_time = order.date_approve
                delta_time = current_time + timedelta(days=leadtime)
                delta_time = delta_time.strftime('%Y-%m-%d %H:%M:%S')
                order.date_planned = delta_time
            else:
                order.date_planned = False

    def action_create_invoice(self):
        res = super(PurchaseOrder, self).action_create_invoice()
        if not self.env.user.is_accounting:
            raise UserError('Hanya Accounting yang bisa membuat Bill')
        return res

    def calculate_line_no(self):
        line_no = 0
        for line in self.order_line:
            if line.product_id.type == 'product':
                line_no += 1
                line.line_no = line_no
                # _logger.info('Calculate PO Line No %s' % line.id)

    def calculate_po_status_beginning(self):
        purchases = self.env['purchase.order'].search([
            ('po_status', '=', False),
            '|',
            ('state', '=', 'purchase'),
            ('state', '=', 'done')
        ])
        for order in purchases:
            sum_qty_received = sum_qty_po = 0
            for po_line in order.order_line:
                sum_qty_po += po_line.product_uom_qty
                sum_qty_received += po_line.qty_received

            if order.summary_qty_po == order.summary_qty_receipt:
                order.po_status = 'terproses'
            elif order.summary_qty_po > order.summary_qty_receipt > 0:
                order.po_status = 'sebagian'
            else:
                order.po_status = 'menunggu'
            _logger.info("Calculate PO Status %s" % order.id)

    def calculate_po_status(self):
        purchases = self.env['purchase.order'].search([
            ('po_status', '!=', 'terproses'),
            # ('id', '=', 213),
        ])
        for order in purchases:
            sum_qty_received = sum_qty_po = 0

            have_outstanding_pick = False
            for pick in order.picking_ids:
                if pick.state == 'draft' or pick.state == 'assigned' or pick.state == 'confirmed' or pick.state == 'waiting':
                    have_outstanding_pick = True

            for po_line in order.order_line:
                sum_qty_po += po_line.product_uom_qty
                sum_qty_received += po_line.qty_received

            if have_outstanding_pick:
                # if order.summary_qty_po == order.summary_qty_receipt:
                #     order.po_status = 'terproses'
                if order.summary_qty_po > order.summary_qty_receipt > 0:
                    order.po_status = 'sebagian'
                else:
                    order.po_status = 'menunggu'
            else:
                order.po_status = 'terproses'
            _logger.info("Calculate PO Status %s" % order.id)

    def _compute_summary_qty(self):
        for order in self:
            sum_qty_po = sum_qty_receipt = 0
            for line in order.order_line:
                sum_qty_po += line.product_uom_qty
                sum_qty_receipt += line.qty_received
            order.summary_qty_po = sum_qty_po
            order.summary_qty_receipt = sum_qty_receipt

    def get_procurement_status(self):
        for purchase_order in self:
            # product_uom_qty = sum_qty_received = 0
            #
            # for order_line in purchase_order.order_line:
            #     product_uom_qty += order_line.product_uom_qty
            #     sum_qty_received += order_line.qty_received

            if purchase_order.summary_qty_po == purchase_order.summary_qty_receipt:
                status = 'Terproses'
            elif purchase_order.summary_qty_po > purchase_order.summary_qty_receipt > 0:
                status = 'Sebagian Diproses'
            else:
                status = 'Menunggu Diproses'
            purchase_order.procurement_status = status
 
    def sale_order_sync(self):
        if not self.sale_order_id:
            return

        purchase_orders = self.search(['&', ('sale_order_id', '=', self.sale_order_id.id), ('id', '!=', self.id)])
        products_exception = []
        for purchase_order in purchase_orders:
            for order_line in purchase_order.order_line:
                products_exception.append(order_line.product_id.id)

        self.order_line.unlink()
        for order_line in self.sale_order_id.order_line:
            if order_line.vendor_id != self.partner_id:
                continue
            if order_line.product_id.id and order_line.product_id.id not in products_exception:
                qty_available = order_line.product_id.qty_onhand_bandengan + order_line.product_id.qty_incoming_bandengan - order_line.product_id.outgoing_qty
                
                # suggest = 'harus beli'
                # if qty_available > order_line.product_qty:
                #     suggest = 'masih cukup'
                
                values = {
                    'order_id': self.id,
                    'product_id': order_line.product_id.id,
                    'name': order_line.product_id.display_name,
                    'product_qty': order_line.product_qty,
                    'qty_available_store': qty_available,
                    # 'suggest': suggest,
                    'so_line_id': order_line.id,
                    'so_id': order_line.order_id.id,
                }
                self.order_line.create(values)
                for order_line in self.order_line:
                    order_line.suggest_purchasing()
                

    def compute_count_line_product(self):
        for order in self:
            count = 0
            for line in order.order_line:
                if line.product_id.type == 'product':
                    count += 1
            if count == 0:
                order.count_line_product = 1
            else:
                order.count_line_product = count

    def compute_delivery_amount(self):
        for order in self:
            amount = 0
            for line in order.order_line:
                if line.product_id.type == 'service':
                    amount += line.price_total
            order.delivery_amount = amount

    def date_deadline_ref_date_planned(self):
        for picking in self.picking_ids:
            if picking.state in ['done', 'cancel']:
                continue
            picking.scheduled_date = self.date_planned
            picking.date_deadline = self.date_planned

    def _check_qty_plafon_product(self):
        for line in self.order_line:
            if not line.product_id:
                continue
            # test = line.product_uom_qty
            # test2 = line.product_id.plafon_qty
            # test3 = test2 + line.product_uom_qty
            if line.product_uom_qty > line.product_id.plafon_qty + line.product_uom_qty and not self.env.user.has_group('indoteknik_custom.group_role_merchandiser'):
                raise UserError('Product '+line.product_id.name+' melebihi plafon, harus Approval MD')

    def button_confirm(self):
        res = super(PurchaseOrder, self).button_confirm()
        current_time = datetime.now()
        self.check_ppn_mix()
        # self.check_data_vendor()

        if self.amount_untaxed >= 50000000 and not self.env.user.has_group('indoteknik_custom.group_role_merchandiser'):
            raise UserError("Hanya Merchandiser yang bisa approve")
            
        if self.total_percent_margin < self.total_so_percent_margin and not self.env.user.has_group('indoteknik_custom.group_role_merchandiser') and not self.env.user.is_leader:
            raise UserError("Beda Margin dengan Sales, harus approval Merchandise")
        if not self.from_apo:
            if not self.matches_so and not self.env.user.has_group('indoteknik_custom.group_role_merchandiser') and not self.env.user.is_leader:
                raise UserError("Tidak ada link dengan SO, harus approval Merchandise")
            
        send_email = False
        self.add_product_to_pricelist()
        for line in self.order_line:
            if not line.product_id.purchase_ok:
                raise UserError("Terdapat barang yang tidak bisa diproses")
            # Validasi pajak
            if not line.taxes_id:
                raise UserError("Masukkan Tax untuk produk")
            for tax in line.taxes_id:
                if tax.type_tax_use != 'purchase':
                    raise UserError("Pastikan Tax Category nya adalah Purchase pada produk %s" % line.product_id.name)
            if line.price_unit != line.price_vendor and line.price_vendor != 0:
                self._send_po_not_sync()
                send_email = True
                break 

        if send_email:
            self._send_mail()

        if self.revisi_po:
            delta_time = current_time - timedelta(days=1) 
            delta_time = delta_time.strftime('%Y-%m-%d %H:%M:%S')
            self.date_approve = delta_time
        
        self.approval_status = 'approved'
        self.po_status = 'menunggu'

        self.calculate_line_no()
        self.approve_by = self.env.user.id

        # override date planned added with two days
        leadtime = self.partner_id.leadtime
        delta_time = current_time + timedelta(days=leadtime)
        delta_time = delta_time.strftime('%Y-%m-%d %H:%M:%S')
        self.date_planned = delta_time
        self.date_deadline_ref_date_planned()
        self.unlink_purchasing_job_state()

        self._check_qty_plafon_product()

        return res
    
    def check_ppn_mix(self):
        reference_taxes = self.order_line[0].taxes_id

        for line in self.order_line:
            if line.taxes_id != reference_taxes:
                raise UserError("PPN harus sama untuk semua baris pada line.")
            
    def check_data_vendor(self):
        vendor = self.partner_id
        if not vendor.email_finance or not vendor.email_sales:
            raise UserError("Email Finance dan Email Sales pada vendor harus diisi")    

    def unlink_purchasing_job_state(self):
        for line in self.order_line:
            purchasing_job_state = self.env['purchasing.job.state'].search([
                ('purchasing_job_id', '=', line.product_id.id)
            ])

            if purchasing_job_state:
                for purchasing_job in purchasing_job_state:
                    purchasing_job.unlink()

    def _send_po_not_sync(self):
        # Mengirim data ke model Po Sync Price jika harga po dan purchase pricelist tidak singkron
        for line in self.order_line:
            if line.price_unit != line.price_vendor and line.price_vendor != 0:
                self.env['po.sync.price'].create([{
                    'order_line_id': line.id,
                }])

    def _send_mail(self):
        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {'in_memory': True})
        worksheet = workbook.add_worksheet()
        
        format6 = workbook.add_format({'font_size': 12, 'align': 'center', 'bg_color': '#D3D3D3', 'bold': True})
        format1 = workbook.add_format({'font_size': 11, 'align': 'center', 'valign': 'vcenter'})
        
        worksheet.set_column(0, 0, 10)  
        worksheet.set_column(1, 1, 20)  
        worksheet.set_column(2, 2, 20)  
        worksheet.set_column(3, 3, 20)  
        worksheet.set_column(4, 4, 15)  
        worksheet.set_column(5, 5, 15)  
        
        worksheet.write('A1', 'PO', format6)
        worksheet.write('B1', 'SKU', format6)
        worksheet.write('C1', 'Product', format6)
        worksheet.write('D1', 'Brand', format6)
        worksheet.write('E1', 'PO Price', format6)
        worksheet.write('F1', 'Purchase Pricelist', format6)
        worksheet.write('G1', 'Created On', format6)
        
        row_number = 1
        po_sync = self.env['po.sync.price'].search([], order='create_date desc')
        for po in po_sync:
            worksheet.write(row_number, 0, po.order_line_id.order_id.name, format1)
            worksheet.write(row_number, 1, po.order_line_id.product_id.default_code, format1)
            worksheet.write(row_number, 2, po.order_line_id.product_id.name, format1)
            worksheet.write(row_number, 3, po.order_line_id.product_id.x_manufacture.x_name, format1)
            worksheet.write(row_number, 4, po.order_line_id.price_unit, format1)
            worksheet.write(row_number, 5, po.order_line_id.price_vendor, format1)
            worksheet.write(row_number, 6, po.create_date.replace(tzinfo=utc).astimezone(timezone('Asia/Jakarta')).strftime('%Y-%m-%d %H:%M:%S'), format1)
            row_number += 1

        workbook.close()
        
        output.seek(0)

        template = self.env.ref('indoteknik_custom.mail_template_po_sync_price')
        template.attachment_ids.unlink()
        attachment_vals = {
            'name': 'Purchase Order.xlsx',
            'datas': base64.b64encode(output.read()),
            'mimetype': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'res_model': 'mail.template',
            'res_id': template.id,
        }
        attachment_id = self.env['ir.attachment'].create(attachment_vals)

        template.attachment_ids = [(4, attachment_id.id)]
        template.send_mail(self.id, force_send=True)

    def po_approve(self):
        # if self.amount_untaxed >= 50000000 and not self.env.user.has_group('indoteknik_custom.group_role_merchandiser'):
        #     raise UserError("Hanya Merchandiser yang bisa approve")
        greater_than_plafon, message = self._get_msg_plafon_qty()
        if self.env.user.is_leader or self.env.user.has_group('indoteknik_custom.group_role_merchandiser'):
            raise UserError("Bisa langsung Confirm")
        elif self.total_percent_margin == self.total_so_percent_margin and self.matches_so and not greater_than_plafon:
            raise UserError("Bisa langsung Confirm")
        else:
            reason = ''
            self.approval_status = 'pengajuan1'
            if self.amount_untaxed >= 50000000:
                reason = 'above 50jt, '
            if self.total_percent_margin < self.total_so_percent_margin:
                reason += 'diff margin, '
            if not self.from_apo and not self.matches_so:
                reason += 'not link with pj and reorder, '
            if not self.matches_so:
                reason += 'not link with so, '
            # Check Plafon Qty and Get Message every Line Product
            if greater_than_plafon:
                reason += message
            # Post a highlighted message to lognote
            self.message_post(
                body=f"<div style='background-color: #fdf2e9; border: 1px solid #f5c6cb; padding: 10px;'>"
                     f"<b>Note (Pinned):</b><br>{reason}</div>",
                subtype_id=self.env.ref("mail.mt_note").id
            )

    def _get_msg_plafon_qty(self):
        message = ''
        greater_than_plafon = False
        for line in self.order_line:
            if not line.product_id:
                continue
            if line.product_uom_qty > line.product_id.plafon_qty:
                message = (message + '\n'+line.product_id.default_code + ' melebihi plafon ('
                           + str(line.product_id.plafon_qty) + ') vs Qty PO ('+str(line.product_uom_qty)+')'
                           + ', ')
                greater_than_plafon = True
        return greater_than_plafon, message

    def re_calculate(self):
        if self.from_apo:
            self.re_calculate_from_apo()
            return
        for line in self.order_line:
            sale_order_line = self.env['sale.order.line'].search([
                ('product_id', 'in', [line.product_id.id]),
                ('order_id', '=', line.order_id.sale_order_id.id)
            ])
            for so_line in sale_order_line:
                so_line.purchase_price = line.price_unit

    def re_calculate_from_apo(self):
        for line in self.order_sales_match_line:
            order_line = self.env['purchase.order.line'].search([
                ('product_id', '=', line.product_id.id),
                ('order_id', '=', line.purchase_order_id.id)
            ], limit=1)
            line.sale_line_id.purchase_price = order_line.price_unit
    
    def button_cancel(self):
        res = super(PurchaseOrder, self).button_cancel()
        self.approval_status = False
        return res

    def compute_total_margin(self):
        for rec in self:
            if rec.from_apo:
                rec.compute_total_margin_from_apo()
                return
            
        sum_so_margin = sum_sales_price = sum_margin = 0
        for line in self.order_line:
            sale_order_line = line.so_line_id
            if not sale_order_line:
                sale_order_line = self.env['sale.order.line'].search([
                    ('product_id', '=', line.product_id.id),
                    ('order_id', '=', line.order_id.sale_order_id.id)
                ], limit=1, order='price_reduce_taxexcl')

            sum_so_margin += sale_order_line.item_margin
            sales_price = sale_order_line.price_reduce_taxexcl * sale_order_line.product_uom_qty
            if sale_order_line.order_id.shipping_cost_covered == 'indoteknik':
                sales_price -= sale_order_line.delivery_amt_line
            if sale_order_line.order_id.fee_third_party > 0:
                sales_price -= sale_order_line.fee_third_party_line
            sum_sales_price += sales_price
            purchase_price = line.price_subtotal
            if line.order_id.delivery_amount > 0:
                purchase_price += line.delivery_amt_line
            if line.order_id.delivery_amt > 0:
                purchase_price += line.order_id.delivery_amt
            real_item_margin = sales_price - purchase_price
            sum_margin += real_item_margin

        if sum_so_margin != 0 and sum_sales_price != 0 and sum_margin != 0:
            self.total_so_margin = sum_so_margin
            self.total_so_percent_margin = round((sum_so_margin / sum_sales_price), 2) * 100
            self.total_margin = sum_margin
            self.total_percent_margin = round((sum_margin / sum_sales_price), 2) * 100
        else:
            self.total_margin = 0
            self.total_percent_margin = 0
            self.total_so_margin = 0
            self.total_so_percent_margin = 0

    def compute_total_margin_from_apo(self):
        sum_so_margin = sum_sales_price = sum_margin = 0
        for line in self.order_sales_match_line:
            po_line = self.env['purchase.order.line'].search([
                ('product_id', '=', line.product_id.id),
                ('order_id', '=', line.purchase_order_id.id)
            ], limit=1)
            sale_order_line = line.sale_line_id
            if not sale_order_line:
                sale_order_line = self.env['sale.order.line'].search([
                    ('product_id', '=', line.product_id.id),
                    ('order_id', '=', line.sale_id.id)
                ], limit=1, order='price_reduce_taxexcl')

            if sale_order_line and po_line:
                so_margin = (line.qty_po / line.qty_so) * sale_order_line.item_margin
                sum_so_margin += so_margin

                sales_price = sale_order_line.price_reduce_taxexcl * line.qty_po
                if sale_order_line.order_id.shipping_cost_covered == 'indoteknik':
                    sales_price -= (sale_order_line.delivery_amt_line / sale_order_line.product_uom_qty) * line.qty_po
                if sale_order_line.order_id.fee_third_party > 0:
                    sales_price -= (sale_order_line.fee_third_party_line / sale_order_line.product_uom_qty) * line.qty_po
                sum_sales_price += sales_price

                purchase_price = po_line.price_subtotal / po_line.product_qty * line.qty_po
                if line.purchase_order_id.delivery_amount > 0:
                    purchase_price += (po_line.delivery_amt_line / po_line.product_qty) * line.qty_po
                if line.purchase_order_id.delivery_amt > 0:
                    purchase_price += line.purchase_order_id.delivery_amt
                real_item_margin = sales_price - purchase_price
                sum_margin += real_item_margin

        if sum_so_margin != 0 and sum_sales_price != 0 and sum_margin != 0:
            self.total_so_margin = sum_so_margin
            self.total_so_percent_margin = round((sum_so_margin / sum_sales_price), 2) * 100
            self.total_margin = sum_margin
            self.total_percent_margin = round((sum_margin / sum_sales_price), 2) * 100

        else:
            self.total_margin = 0
            self.total_percent_margin = 0
            self.total_so_margin = 0
            self.total_so_percent_margin = 0

    def compute_amt_total_without_service(self):
        for order in self:
            sum_price_total = 0
            for line in order.order_line:
                if line.product_id.type == 'product':
                    sum_price_total += line.price_total
            order.amount_total_without_service = sum_price_total