52
52
def _get_default_last_date(self):
53
last_date = self.pool.get('ir.config_parameter').get_param(self.env.cr, self.env.uid, 'last.date.update.cost.policy')
53
last_date = self.pool.get('ir.config_parameter').get_param(self.env.cr, self.env.uid,
54
'last.date.update.cost.policy')
57
58
def update_policy(self):
59
60
query = """Truncate Table poliza_ids;"""
60
61
self.env.cr.execute(query)
62
company_id = 3 # NUTRIVE
63
company_id = 3 # NUTRIVE
65
66
WHERE aml.company_id = %s AND aml.date >= '%s'
66
AND aml.account_id IN (fn_get_product_expense_account( aml.product_id, aml.company_id), fn_get_product_output_account(product_id, aml.company_id))
67
AND aml.account_id IN (fn_get_product_expense_account( aml.product_id, aml.company_id),
68
fn_get_product_output_account(product_id, aml.company_id))
67
69
AND aml.product_uom_id <> pt.uom_id AND aj.type = 'sale'
68
70
AND pt.uos_coeff > 0
69
71
And aml.id not in (select id from poliza_historia_ids)
72
74
query = """Insert Into poliza_ids
73
75
SELECT aml.id as id_det, am.id,
74
pp.id as product_id, aj.name journal, am.name poliza, rp.name cliente, pt.name product, aml.date, aml.ref, aml.debit, aml.credit, aml.quantity, ROUND( 1 / pt.uos_coeff, 2 ) coef,
75
CASE WHEN debit > 0 THEN round(debit / quantity,2) ELSE round(credit / quantity,2) END costo,
76
ROUND( aml.quantity * (1 / pt.uos_coeff), 2 ) realqty,
77
Round((ROUND((aml.quantity * (1 / pt.uos_coeff)),2)) * (CASE WHEN debit > 0 THEN round(debit / quantity,2) ELSE round(credit / quantity,2) END),2) costoreal
76
pp.id as product_id, aj.name journal, am.name poliza, rp.name cliente,
77
pt.name product, aml.date, aml.ref, aml.debit, aml.credit, aml.quantity,
78
ROUND( 1 / pt.uos_coeff, 2 ) coef,
79
CASE WHEN debit > 0 THEN round(debit / quantity,2) ELSE round(credit / quantity,2) END costo,
80
ROUND( aml.quantity * (1 / pt.uos_coeff), 2 ) realqty,
81
Round((ROUND((aml.quantity * (1 / pt.uos_coeff)),2)) * (CASE WHEN debit > 0 THEN
82
round(debit / quantity,2) ELSE round(credit / quantity,2) END),2) costoreal
78
83
FROM account_move_line aml INNER JOIN product_product pp ON (aml.product_id = pp.id)
79
84
INNER JOIN product_template pt ON (pp.product_tmpl_id = pt.id)
80
85
INNER JOIN account_account aa ON (aml.account_id = aa.id)
88
93
# Inserta Procesadas
89
94
query = """Insert Into poliza_historia_ids
91
FROM poliza_ids ids;"""% (datetime.now().strftime('%Y-%m-%d %H:%M:%S'))#self.date_last_process)
96
FROM poliza_ids ids;""" % (datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
92
97
self.env.cr.execute(query)
96
query = """UPDATE account_move_line AS aml
97
SET debit = (CASE WHEN aml.debit > 0 THEN ids.CostoReal ELSE 0 End), credit = (CASE WHEN aml.credit > 0 THEN ids.CostoReal ELSE 0 End)
100
query = """UPDATE account_move_line AS aml SET
101
debit = (CASE WHEN aml.debit > 0 THEN ids.CostoReal ELSE 0 End),
102
credit = (CASE WHEN aml.credit > 0 THEN ids.CostoReal ELSE 0 End),
103
balance = (CASE WHEN aml.credit > 0 THEN ids.CostoReal * -1 ELSE ids.CostoReal End)
98
104
FROM poliza_ids ids
99
105
WHERE ids.id = aml.id;"""
100
106
self.env.cr.execute(query)
103
109
record_ids = self.env['ir.config_parameter'].search([('key', '=', 'last.date.update.cost.policy')])
104
110
for record in record_ids:
112
118
def execute_report(self):
113
company_id = 3 # NUTRIVE
119
company_id = 3 # NUTRIVE
115
121
where_filter = """
116
122
WHERE aml.company_id = %s AND aml.date >= '%s'
117
AND aml.account_id IN (fn_get_product_expense_account( aml.product_id, aml.company_id), fn_get_product_output_account(product_id, aml.company_id))
123
AND aml.account_id IN (fn_get_product_expense_account( aml.product_id, aml.company_id),
124
fn_get_product_output_account(product_id, aml.company_id))
118
125
AND aml.product_uom_id <> pt.uom_id AND aj.type = 'sale'
119
126
AND pt.uos_coeff > 0
120
127
And aml.id not in (select id from poliza_historia_ids)
121
128
ORDER BY aml.date, aml.id;""" % (company_id, self.date_last_process)
123
130
query = """SELECT aml.id as id_det, am.id,
124
pp.id as product_id, aj.name journal, am.name poliza, rp.name cliente, pt.name product, aml.date, aml.ref, aml.debit, aml.credit, aml.quantity, ROUND( 1 / pt.uos_coeff, 2 ) coef,
125
CASE WHEN debit > 0 THEN round(debit / quantity,2) ELSE round(credit / quantity,2) END costo,
126
ROUND( aml.quantity * (1 / pt.uos_coeff), 2 ) realqty,
127
Round((ROUND((aml.quantity * (1 / pt.uos_coeff)),2)) * (CASE WHEN debit > 0 THEN round(debit / quantity,2) ELSE round(credit / quantity,2) END),2) costoreal
131
pp.id as product_id, aj.name journal, am.name poliza, rp.name cliente,
132
pt.name product, aml.date, aml.ref, aml.debit, aml.credit, aml.quantity,
133
ROUND( 1 / pt.uos_coeff, 2 ) coef,
134
CASE WHEN debit > 0 THEN round(debit / quantity,2) ELSE round(credit / quantity,2) END costo,
135
ROUND( aml.quantity * (1 / pt.uos_coeff), 2 ) realqty,
136
Round((ROUND((aml.quantity * (1 / pt.uos_coeff)),2)) * (CASE WHEN debit > 0 THEN
137
round(debit / quantity,2) ELSE round(credit / quantity,2) END),2) costoreal
128
138
FROM account_move_line aml INNER JOIN product_product pp ON (aml.product_id = pp.id)
129
139
INNER JOIN product_template pt ON (pp.product_tmpl_id = pt.id)
130
140
INNER JOIN account_account aa ON (aml.account_id = aa.id)
133
143
INNER JOIN res_partner rp ON (aml.partner_id = rp.id)
135
145
query = query + where_filter
137
147
self.env.cr.execute(query)
139
#query = """Select * From poliza_ids;"""
140
#self.env.cr.execute(query)
149
# query = """Select * From poliza_ids;"""
150
# self.env.cr.execute(query)
141
151
registros = self.env.cr.fetchall()
142
152
if len(registros) > 0:
145
155
for move in registros:
147
157
'cost_policy_report_id': self.id,
148
'move_line_id': move[0] or 0,
158
'move_line_id': move[0] or 0,
149
159
'move_id': move[1] or 0,
150
160
'product_id': move[2] or 0,
151
161
'journal': move[3] or '',