Please let me know How to do this button function in EXPNext.
Because I’m new to this functionality. Kindly help me how to do this concept in Erpnext.
def track_results(self):
for monthly_scheme in self:
monthly_scheme_results = self.env['monthly.scheme.results'].search([('monthly_schemes_id', '=', monthly_scheme.id)])
monthly_scheme_results_lines = self.env['monthly.scheme.results.line'].search([('monthly_scheme_results_id', 'in',
monthly_scheme_results)])
if monthly_scheme_results_lines:
monthly_scheme_results_details = self.env['monthly.scheme.results.detail'].search([('monthly_scheme_results_line_id','in',monthly_scheme_results_lines)])
if monthly_scheme_results_details:
monthly_scheme_results_details.unlink()
monthly_scheme_results_lines.unlink()
category_lines_obj = self.env['category.lines']
sub_category_lines_obj = self.env['sub.category.lines']
product_lines_obj = self.env['product.lines']
print(('date_invoice', '<=', monthly_scheme.end_date),('date_invoice', '>=', monthly_scheme.start_date),'::::::::')
invoices = self.env['account.invoice'].search([('state', 'in', ['open', 'paid']),('date_invoice', '<=', monthly_scheme.end_date),
('date_invoice', '>=', monthly_scheme.start_date)]).ids
print(invoices,'invoicesinvoices')
category_lines = category_lines_obj.search([('monthly_schemes_id', '=', monthly_scheme.id)]).ids
sub_category_lines = sub_category_lines_obj.search([('category_lines_id', 'in', category_lines)]).ids
product_lines = product_lines_obj.search([('sub_category_lines_id', 'in', sub_category_lines)]).ids
closed_scheme_category_lines = category_lines_obj.search([('id', 'in', category_lines),
('closed_scheme', '=', True)]).ids
closed_scheme_sub_category_lines = sub_category_lines_obj.search([('id', 'in', sub_category_lines),
('closed_scheme', '=', True)]).ids
closed_scheme_product_lines = product_lines_obj.search([('id', 'in', product_lines),
('closed_scheme', '=', True)]).ids
customers_in_this_period = []
for invoice in self.env['account.invoice'].browse(invoices):
if not invoice.partner_id.id in customers_in_this_period:
customers_in_this_period.append(invoice.partner_id.id)
monthly_scheme_results_lines = []
for customer in self.env['res.partner'].browse(customers_in_this_period):
credit_note_total = 0.0
monthly_scheme_results_details = []
monthly_scheme_gifts_details = []
products_accounted = []
for product_line in product_lines_obj.browse(closed_scheme_product_lines):
products_accounted.append(product_line.product_id.id)
self.env.cr.execute('WITH product_sales_query AS \
(SELECT product_id, sum(quantity) AS quantity, sum(actual_quantity*quantity) as actual_quantity, \
sum(round(price_subtotal)) AS price_subtotal, sum(price_total) AS price_total, \
sum(price_discount) AS price_discount \
FROM account_invoice_line AS ail \
JOIN account_invoice ai ON (ail.invoice_id = ai.id) \
WHERE \
ai.state IN %s AND \
ai.type = %s AND \
ai.partner_id = %s AND \
ail.product_id = %s AND \
ai.date_invoice >= %s AND \
ai.date_invoice <= %s GROUP BY product_id) \
SELECT prod.id AS product_id, sum(pss.actual_quantity) AS actual_quantity, sum(pss.price_total) AS price_total, sum(pss.price_subtotal) AS price_subtotal \
FROM product_sales_query pss \
JOIN product_product prod ON (pss.product_id = prod.id) GROUP BY prod.id',
(('open', 'paid'), 'out_invoice', customer.id, product_line.product_id.id,
monthly_scheme.start_date, monthly_scheme.end_date))
result = self.env.cr.dictfetchall()
if result and result[0]['actual_quantity']:
volume = result[0]['actual_quantity']
print(volume,product_line.id,'gghhfggddjjjj')
closed_scheme_rebates = self.env['closed.scheme.rebate'].search([('vol', '<=', volume), ('product_lines_id', '=', product_line.id)], order='vol desc').ids
print(closed_scheme_rebates, 'closed_scheme_rebatesclosed_scheme_rebatesclosed_scheme_rebates')
if closed_scheme_rebates:
closed_scheme_rebate = self.env['closed.scheme.rebate'].browse(closed_scheme_rebates)[0]
print(closed_scheme_rebate,'closed_scheme_rebateclosed_scheme_rebateclosed_scheme_rebate')
if not closed_scheme_rebate.ignore:
rebate = closed_scheme_rebate.rebate
credit_note = volume * rebate
credit_note_total += credit_note
monthly_scheme_results_details.append((0, 0, {
'name': product_line.product_id.variant_name,
'credit_note': credit_note,
'customer_volume': volume,
'rebate': rebate,
'rebate_volume': closed_scheme_rebate.vol
}))
else:
monthly_scheme_gifts_details.append((0, 0, {
'name': product_line.product_id.variant_name,
'customer_volume': volume,
'gift_name': closed_scheme_rebate.gift_name,
'rebate_volume': closed_scheme_rebate.vol
}))
monthly_scheme_results_lines.append((0, 0, {
'partner_id': customer.id,
'gift_name': closed_scheme_rebate.gift_name,
'state': 'draft',
'ignore': 'True',
'monthly_scheme_results_details': monthly_scheme_gifts_details
}))
categ_accounted = []
for sub_category_line in sub_category_lines_obj.browse(closed_scheme_sub_category_lines):
print(sub_category_line.category_id.id,'testing')
categ_accounted.append(sub_category_line.category_id.id)
products = self.env['product.product'].search([('id', 'not in', products_accounted), ('categ_id', '=', sub_category_line.category_id.id)]).ids
(list(products))
if products:
self.env.cr.execute('WITH product_sales_query AS \
(SELECT product_id, sum(quantity) AS quantity, sum(actual_quantity*quantity) as actual_quantity, \
sum(round(price_subtotal)) AS price_subtotal, sum(price_total) AS price_total, \
sum(price_discount) AS price_discount \
FROM account_invoice_line AS ail \
JOIN account_invoice ai ON (ail.invoice_id = ai.id) \
WHERE \
ai.state IN %s AND \
ai.type = %s AND \
ai.partner_id = %s AND \
ail.product_id IN %s AND \
ai.date_invoice >= %s AND \
ai.date_invoice <= %s GROUP BY product_id) \
SELECT pc.id AS categ_id, sum(pss.actual_quantity) AS actual_quantity, sum(pss.price_total) AS price_total, sum(pss.price_subtotal) AS price_subtotal \
FROM product_sales_query pss \
JOIN product_product prod ON (pss.product_id = prod.id) \
JOIN product_template pt ON (prod.product_tmpl_id = pt.id) \
JOIN product_category pc ON (pt.categ_id = pc.id) \
GROUP BY pc.id',
(('open', 'paid'), 'out_invoice', customer.id, tuple(products),
monthly_scheme.start_date, monthly_scheme.end_date))
result = self.env.cr.dictfetchall()
if result and result[0]['actual_quantity']:
volume = result[0]['actual_quantity']
print(volume,'subcategory')
closed_scheme_rebates = self.env['closed.scheme.rebate'].search([('vol', '<=', volume), ('sub_category_lines_id', '=', sub_category_line.id)], order='vol desc').ids
print(closed_scheme_rebates,'closed_scheme_rebatesclosed_scheme_rebatesclosed_scheme_rebates')
if closed_scheme_rebates:
closed_scheme_rebate = self.env['closed.scheme.rebate'].browse(closed_scheme_rebates)[0]
if not closed_scheme_rebate.ignore:
rebate = closed_scheme_rebate.rebate
credit_note = volume * rebate
credit_note_total += credit_note
monthly_scheme_results_details.append((0, 0, {
'name': sub_category_line.category_id.parent_id.name + ' / ' + sub_category_line.category_id.name,
'credit_note': credit_note,
'customer_volume': volume,
'rebate': rebate,
'rebate_volume': closed_scheme_rebate.vol
}))
else:
monthly_scheme_gifts_details.append((0, 0, {
'name': sub_category_line.category_id.parent_id.name + ' / ' + sub_category_line.category_id.name,
'credit_note': credit_note,
'customer_volume': volume,
'gift_name': closed_scheme_rebate.gift_name,
'rebate_volume': closed_scheme_rebate.vol
}))
monthly_scheme_results_lines.append((0, 0, {
'partner_id': customer.id,
'gift_name': closed_scheme_rebate.gift_name,
'state': 'draft',
'ignore': 'True',
'monthly_scheme_results_details': monthly_scheme_gifts_details
}))
for category_line in category_lines_obj.browse(closed_scheme_category_lines):
print(categ_accounted,'categ_accountedcateg_accountedcateg_accountedcateg_accounted')
sub_categories_sr = self.env['product.category'].search([('id', 'not in', categ_accounted), ('parent_id', '=', category_line.category_id.id)]).ids
products = self.env['product.product'].search([('id', 'not in', products_accounted), ('categ_id', 'in', sub_categories_sr)]).ids
if products:
self.env.cr.execute('WITH product_sales_query AS \
(SELECT product_id, sum(quantity) AS quantity, sum(actual_quantity*quantity) as actual_quantity, \
sum(round(price_subtotal)) AS price_subtotal, sum(price_total) AS price_total, \
sum(price_discount) AS price_discount \
FROM account_invoice_line AS ail \
JOIN account_invoice ai ON (ail.invoice_id = ai.id) \
WHERE \
ai.state IN %s AND \
ai.type = %s AND \
ai.partner_id = %s AND \
ail.product_id IN %s AND \
ai.date_invoice >= %s AND \
ai.date_invoice <= %s GROUP BY product_id) \
SELECT parent.id AS parent_id, sum(pss.actual_quantity) AS actual_quantity, sum(pss.price_total) AS price_total, sum(pss.price_subtotal) AS price_subtotal \
FROM product_sales_query pss \
JOIN product_product prod ON (pss.product_id = prod.id) \
JOIN product_template pt ON (prod.product_tmpl_id = pt.id) \
JOIN product_category pc ON (pt.categ_id = pc.id) \
JOIN product_category parent ON (pc.parent_id = parent.id) \
GROUP BY parent.id',
(('open', 'paid'), 'out_invoice', customer.id, tuple(products),
monthly_scheme.start_date, monthly_scheme.end_date))
result = self.env.cr.dictfetchall()
if result and result[0]['actual_quantity']:
volume = result[0]['actual_quantity']
closed_scheme_rebates = self.env['closed.scheme.rebate'].search([('vol', '<=', volume), ('category_lines_id', '=', category_line.id)], order='vol desc')
if closed_scheme_rebates:
closed_scheme_rebate = self.env['closed.scheme.rebate'].browse(closed_scheme_rebates)[0]
if not closed_scheme_rebate.ignore:
rebate = closed_scheme_rebate.rebate
credit_note = volume * rebate
credit_note_total += credit_note
monthly_scheme_results_details.append((0, 0, {
'name': category_line.category_id.name,
'credit_note': credit_note,
'customer_volume': volume,
'rebate': rebate,
'rebate_volume': closed_scheme_rebate.vol
}))
else:
gift_name = closed_scheme_rebate.gift_name
monthly_scheme_gifts_details.append((0, 0, {
'name': category_line.category_id.name,
'customer_volume': volume,
'gift_name': closed_scheme_rebate.gift_name,
'rebate_volume': closed_scheme_rebate.vol
}))
monthly_scheme_results_lines.append((0, 0, {
'partner_id': customer.id,
'gift_name': closed_scheme_rebate.gift_name,
'state': 'draft',
'ignore': 'True',
'monthly_scheme_results_details': monthly_scheme_gifts_details
}))
if credit_note_total:
monthly_scheme_results_lines.append((0, 0, {
'partner_id': customer.id,
'credit_note': credit_note_total,
'state': 'draft',
'monthly_scheme_results_details': monthly_scheme_results_details
}))
print(monthly_scheme_results_lines,'monthly_scheme_results_linesmonthly_scheme_results_linesmonthly_scheme_results_lines')
if not monthly_scheme_results:
self.env['monthly.scheme.results'].create({
'monthly_schemes_id': monthly_scheme.id,
'monthly_scheme_results_lines': monthly_scheme_results_lines,
'state': 'draft',
})
else:
monthly_scheme_results.write({'monthly_scheme_results_lines': monthly_scheme_results_lines})
return True