c2c-oerpscenario team mailing list archive

[Carlos Incaser <723448@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

Product_margin module not working properly.
The values shown both purchases and sales are always the purchases, because by using the multi attribute passed all fields at once.
The performance is also improved if instead of using the "res [val.id]. get ('turnover', val.turnover)" use the value already obtained "res [val.id] ['turnover']"
To solve the problem and improve the performance suggest these changes from the line 55:

            sqlstr="""select
                    avg(l.price_subtotal/l.quantity) as avg_unit_price,
                    sum(l.quantity) as num_qty,
                    sum(l.quantity * (l.price_subtotal/l.quantity)) as total,
                    sum(l.quantity * product.list_price) as sale_expected,
                    sum(l.quantity * product.standard_price) as normal_cost
                from account_invoice_line l
                left join account_invoice i on (l.invoice_id = i.id)
                left join product_template product on (product.id=l.product_id)
                where l.product_id = %s and i.state in %s and i.type IN %s and i.date_invoice>=%s and i.date_invoice<=%s
                """
            invoice_types = ('out_invoice', 'in_refund')
            cr.execute(sqlstr, (val.id, states, invoice_types, date_from, date_to))
            result = cr.fetchall()[0]
            res[val.id]['sale_avg_price'] = result[0] and result[0] or 0.0
            res[val.id]['sale_num_invoiced'] = result[1] and result[1] or 0.0
            res[val.id]['turnover'] = result[2] and result[2] or 0.0
            res[val.id]['sale_expected'] = result[3] and result[3] or 0.0
            res[val.id]['sales_gap'] = res[val.id]['sale_expected']-res[val.id]['turnover']

            invoice_types = ('in_invoice', 'out_refund')
            cr.execute(sqlstr, (val.id, states, invoice_types, date_from, date_to))
            result = cr.fetchall()[0]
            res[val.id]['purchase_avg_price'] = result[0] and result[0] or 0.0
            res[val.id]['purchase_num_invoiced'] = result[1] and result[1] or 0.0
            res[val.id]['total_cost'] = result[2] and result[2] or 0.0
            res[val.id]['normal_cost'] = result[4] and result[4] or 0.0
            res[val.id]['purchase_gap'] = res[val.id]['normal_cost']-res[val.id]['total_cost']

            if 'total_margin' in field_names:
                res[val.id]['total_margin'] = res[val.id]['turnover'] - res[val.id]['total_cost']
            if 'expected_margin' in field_names:
                res[val.id]['expected_margin'] = res[val.id]['sale_expected'] - res[val.id]['normal_cost']
            if 'total_margin_rate' in field_names:
                res[val.id]['total_margin_rate'] = res[val.id]['turnover'] and res[val.id]['total_margin'] * 100 / res[val.id]['turnover'] or 0.0
            if 'expected_margin_rate' in field_names:
                res[val.id]['expected_margin_rate'] = res[val.id]['sale_expected'] and res[val.id]['expected_margin'] * 100 /  res[val.id]['sale_expected'] or 0.0

** Affects: openobject-addons
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of C2C
OERPScenario, which is subscribed to the OpenERP Project Group.
https://bugs.launchpad.net/bugs/723448

Title:
  product_margin purchases values in sales

Status in OpenERP Modules (addons):
  New

Bug description:
  Product_margin module not working properly.
  The values shown both purchases and sales are always the purchases, because by using the multi attribute passed all fields at once.
  The performance is also improved if instead of using the "res [val.id]. get ('turnover', val.turnover)" use the value already obtained "res [val.id] ['turnover']"
  To solve the problem and improve the performance suggest these changes from the line 55:

              sqlstr="""select
                      avg(l.price_subtotal/l.quantity) as avg_unit_price,
                      sum(l.quantity) as num_qty,
                      sum(l.quantity * (l.price_subtotal/l.quantity)) as total,
                      sum(l.quantity * product.list_price) as sale_expected,
                      sum(l.quantity * product.standard_price) as normal_cost
                  from account_invoice_line l
                  left join account_invoice i on (l.invoice_id = i.id)
                  left join product_template product on (product.id=l.product_id)
                  where l.product_id = %s and i.state in %s and i.type IN %s and i.date_invoice>=%s and i.date_invoice<=%s
                  """
              invoice_types = ('out_invoice', 'in_refund')
              cr.execute(sqlstr, (val.id, states, invoice_types, date_from, date_to))
              result = cr.fetchall()[0]
              res[val.id]['sale_avg_price'] = result[0] and result[0] or 0.0
              res[val.id]['sale_num_invoiced'] = result[1] and result[1] or 0.0
              res[val.id]['turnover'] = result[2] and result[2] or 0.0
              res[val.id]['sale_expected'] = result[3] and result[3] or 0.0
              res[val.id]['sales_gap'] = res[val.id]['sale_expected']-res[val.id]['turnover']

              invoice_types = ('in_invoice', 'out_refund')
              cr.execute(sqlstr, (val.id, states, invoice_types, date_from, date_to))
              result = cr.fetchall()[0]
              res[val.id]['purchase_avg_price'] = result[0] and result[0] or 0.0
              res[val.id]['purchase_num_invoiced'] = result[1] and result[1] or 0.0
              res[val.id]['total_cost'] = result[2] and result[2] or 0.0
              res[val.id]['normal_cost'] = result[4] and result[4] or 0.0
              res[val.id]['purchase_gap'] = res[val.id]['normal_cost']-res[val.id]['total_cost']

              if 'total_margin' in field_names:
                  res[val.id]['total_margin'] = res[val.id]['turnover'] - res[val.id]['total_cost']
              if 'expected_margin' in field_names:
                  res[val.id]['expected_margin'] = res[val.id]['sale_expected'] - res[val.id]['normal_cost']
              if 'total_margin_rate' in field_names:
                  res[val.id]['total_margin_rate'] = res[val.id]['turnover'] and res[val.id]['total_margin'] * 100 / res[val.id]['turnover'] or 0.0
              if 'expected_margin_rate' in field_names:
                  res[val.id]['expected_margin_rate'] = res[val.id]['sale_expected'] and res[val.id]['expected_margin'] * 100 /  res[val.id]['sale_expected'] or 0.0