from odoo import fields, models, api, tools, _ import logging from datetime import datetime _logger = logging.getLogger(__name__) class PurchasingJob(models.Model): _name = 'v.purchasing.job' _auto = False _rec_name = 'product_id' product_id = fields.Many2one('product.product', string="Product") vendor_id = fields.Many2one('res.partner', string="Vendor") brand = fields.Char(string='Brand') item_code = fields.Char(string='Item Code') product = fields.Char(string='Product Name') onhand = fields.Float(string='OnHand') incoming = fields.Float(string="Incoming") outgoing = fields.Float(string="Outgoing") action = fields.Char(string="Status") status_apo = fields.Selection([ ('not_apo', 'Belum APO'), ('apo', 'APO') ], string='APO?') purchase_representative_id = fields.Many2one('res.users', string="Purchase Representative", readonly=True) note = fields.Char(string="Note Detail") def redirect_to_pjs(self): states = self.env['purchasing.job.state'].search([ ('purchasing_job_id', '=', self.id), ],limit=1, order='id desc') return { 'name': _('Purchasing Job State'), 'view_mode': 'form', 'res_model': 'purchasing.job.state', 'target': 'new', 'type': 'ir.actions.act_window', 'res_id': states.id, } def init(self): tools.drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute(""" CREATE OR REPLACE VIEW %s AS ( WITH latest_purchase_orders AS ( SELECT pol.product_id, po.user_id, ROW_NUMBER() OVER (PARTITION BY po.partner_id ORDER BY po.create_date DESC) AS order_rank FROM purchase_order po RIGHT JOIN purchase_order_line pol ON pol.order_id = po.id LEFT JOIN res_partner rp ON rp.id = po.partner_id ), random_user_ids AS ( SELECT DISTINCT CASE WHEN vendor_id = 5571 THEN 27 WHEN vendor_id = 9688 THEN 397 ELSE (CASE WHEN random() < 0.5 THEN 397 ELSE 1036 END) END AS user_id, vendor_id FROM ( SELECT sol.vendor_id FROM v_sales_outstanding vso LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id ) AS sub WHERE sub.vendor_id IS NOT NULL ) SELECT pmp.product_id AS id, pmp.product_id, sub.vendor_id, pmp.brand, pmp.item_code, pmp.product, MAX(pmp.onhand) AS onhand, MAX(pmp.incoming) AS incoming, MAX(pmp.outgoing) AS outgoing, pmp.action, MAX(pjs.status_apo) AS status_apo, MAX(pjs.note) AS note, ru.user_id AS purchase_representative_id FROM v_procurement_monitoring_by_product pmp LEFT JOIN purchasing_job_state pjs ON pjs.purchasing_job_id = pmp.product_id LEFT JOIN ( SELECT vso.product_id, sol.vendor_id FROM v_sales_outstanding vso LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id ) AS sub ON sub.product_id = pmp.product_id LEFT JOIN latest_purchase_orders po ON po.product_id = pmp.product_id LEFT JOIN random_user_ids ru ON ru.vendor_id = sub.vendor_id OR (ru.vendor_id IS NULL AND sub.vendor_id != 9688) WHERE pmp.action = 'kurang' AND sub.vendor_id IS NOT NULL GROUP BY pmp.product_id, pmp.brand, pmp.item_code, pmp.product, pmp.action, sub.vendor_id, ru.user_id ) """ % self._table) def open_form_multi_generate_request_po(self): action = self.env['ir.actions.act_window']._for_xml_id('indoteknik_custom.action_purchasing_job_multi_update') action['context'] = { 'product_ids': [x.id for x in self] } return action def generate_request_po(self): # print(1) # TODO create document automatic purchase current_time = datetime.utcnow() automatic_purchase = self.env['automatic.purchase'].create([{ 'apo_type': 'regular', 'date_doc': current_time, }]) count = 0 for job in self: print(job.product_id.name) qty_purchase = job.outgoing - (job.onhand + job.incoming) qty_available = (job.onhand + job.incoming) - job.outgoing domain = [ ('product_id.id', '=', job.product_id.id), ] orderby = 'count_trx_po desc, count_trx_po_vendor desc' purchase_pricelist = self.env['purchase.pricelist'].search(domain, order=orderby, limit=1) vendor_id = purchase_pricelist.vendor_id price, taxes = automatic_purchase._get_valid_purchase_price(purchase_pricelist) last_po_line = self.env['purchase.order.line'].search([('product_id', '=', job.product_id.id), ('order_id.state', '=', 'done')], order='id desc', limit=1) self.env['automatic.purchase.line'].create([{ 'automatic_purchase_id': automatic_purchase.id, 'product_id': job.product_id.id, 'qty_purchase': qty_purchase, 'qty_available': qty_available, 'partner_id': job.vendor_id.id, 'last_price': price, 'taxes_id': taxes, 'subtotal': qty_purchase * price, 'last_order_id': last_po_line.order_id.id, 'last_orderline_id': last_po_line.id, 'brand_id': job.product_id.product_tmpl_id.x_manufacture.id }]) automatic_purchase._create_sales_matching() automatic_purchase._create_sync_purchasing_job(job) count += 1 _logger.info('Create Automatic Purchase Line %s' % job.product_id.name) return automatic_purchase.id class OutstandingSales(models.Model): _name = 'v.sales.outstanding' _auto = False _rec_name = 'move_id' id = fields.Integer() move_id = fields.Many2one('stock.move', string='Move') picking_id = fields.Many2one('stock.picking', string='Picking') product_id = fields.Many2one('product.product', string='Product') sale_id = fields.Many2one('sale.order', string='Sale') sale_line_id = fields.Many2one('sale.order.line', string='Sale Line') partner_id = fields.Many2one('res.partner', string='Partner') partner_invoice_id = fields.Many2one('res.partner', string='Invoice Partner') salesperson_id = fields.Many2one('res.users', string='Salesperson') origin = fields.Char(string='Origin') salesperson = fields.Char(string='Sales Name') item_code = fields.Char(string='Item Code') product = fields.Char(string='Product') outgoing = fields.Float(string='Outgoing') brand = fields.Char(string='Brand') invoice_partner = fields.Char(string='Invoice Partner') def init(self): tools.drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute(""" CREATE OR REPLACE VIEW v_sales_outstanding AS ( select sm.id, sm.id as move_id, sp.id as picking_id, sm.product_id, so.id as sale_id, sol.id as sale_line_id, rp.id as partner_id, so.user_id as salesperson_id, so.partner_invoice_id, sp.origin, rp2.name as salesperson, coalesce(pp.default_code, pt.default_code) as item_code, pt.name as product, sm.product_uom_qty as outgoing, xm.x_name as brand, rp.name as invoice_partner from stock_move sm join stock_picking sp on sp.id = sm.picking_id join sale_order_line sol on sol.id = sm.sale_line_id join sale_order so on so.id = sol.order_id join res_partner rp on rp.id = so.partner_invoice_id join res_users ru on ru.id = so.user_id join res_partner rp2 on rp2.id = ru.partner_id join product_product pp on pp.id = sm.product_id join product_template pt on pt.id = pp.product_tmpl_id left join x_manufactures xm on xm.id = pt.x_manufacture where sp.state in ('draft', 'waiting', 'confirmed', 'assigned') and sp.name like '%OUT%' ) """)