1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
|
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%'
)
""")
|
