new module : product margin

bzr revid: hmo@tinyerp.com-20081003142828-kcwd23dddegc9rz2

6 files changed, 380 insertions, 0 deletions

diff --git a/addons/product_margin/__init__.py b/addons/product_margin/__init__.py
new file mode 100644
index 00000000000..5c3a1b13af2
--- /dev/null
+++ b/addons/product_margin/__init__.py
@@ -0,0 +1,34 @@
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+# Copyright (c) 2006 TINY SPRL. (http://tiny.be) All Rights Reserved.
+#                    Fabien Pinckaers <fp@tiny.Be>
+#
+# WARNING: This program as such is intended to be used by professional
+# programmers who take the whole responsability of assessing all potential
+# consequences resulting from its eventual inadequacies and bugs
+# End users who are looking for a ready-to-use solution with commercial
+# garantees and support are strongly adviced to contract a Free Software
+# Service Company
+#
+# This program is Free Software; you can redistribute it and/or
+# modify it under the terms of the GNU General Public License
+# as published by the Free Software Foundation; either version 2
+# of the License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
+#
+##############################################################################
+
+import wizard
+import product_margin
+
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
+
diff --git a/addons/product_margin/__terp__.py b/addons/product_margin/__terp__.py
new file mode 100644
index 00000000000..5aac7a747fd
--- /dev/null
+++ b/addons/product_margin/__terp__.py
@@ -0,0 +1,14 @@
+# -*- encoding: utf-8 -*-
+{
+    "name":"Margins in Product",
+    "version":"1.0",
+    "author":"Tiny",
+    "category":"Custom",
+    "depends":["product"],
+    "demo_xml":[],
+    "update_xml":["product_margin_view.xml"],
+    "active": False,
+    "installable": True,
+}
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
+
diff --git a/addons/product_margin/product_margin.py b/addons/product_margin/product_margin.py
new file mode 100644
index 00000000000..a785f1e17b8
--- /dev/null
+++ b/addons/product_margin/product_margin.py
@@ -0,0 +1,139 @@
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+# Copyright (c) 2004-2006 TINY SPRL. (http://tiny.be) All Rights Reserved.
+#
+# $Id: partner.py 1007 2005-07-25 13:18:09Z kayhman $
+#
+# WARNING: This program as such is intended to be used by professional
+# programmers who take the whole responsability of assessing all potential
+# consequences resulting from its eventual inadequacies and bugs
+# End users who are looking for a ready-to-use solution with commercial
+# garantees and support are strongly adviced to contract a Free Software
+# Service Company
+#
+# This program is Free Software; you can redistribute it and/or
+# modify it under the terms of the GNU General Public License
+# as published by the Free Software Foundation; either version 2
+# of the License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
+#
+##############################################################################
+
+from osv import fields,osv
+import pooler
+from tools import config
+import time
+
+class product_product(osv.osv):
+    _inherit = "product.product"     
+    
+    
+    def _product_margin(self, cr, uid, ids, field_names, arg, context):
+        res = {}               
+        for val in self.browse(cr, uid, ids,context=context):
+            res[val.id] = {}
+            date_from=context.get('date_from', time.strftime('%Y-01-01'))	
+            date_to=context.get('date_to', time.strftime('%Y-12-31'))	
+            invoice_state=context.get('invoice_state', 'open')	
+            if 'date_from' in field_names:
+            	res[val.id]['date_from']=date_from
+            if 'date_to' in field_names:
+                res[val.id]['date_to']=date_to
+            if 'invoice_state' in field_names:
+            	res[val.id]['invoice_state']=invoice_state
+
+            
+            invoice_types=[]
+            states=[]
+            if invoice_state=='draft_open':
+                states=['draft','open']
+            elif invoice_state=='paid':
+                states=['paid']
+            elif invoice_state=='open':
+                states=['open']
+            
+            if 'sale_avg_price' in field_names or 'sale_num_invoiced' in field_names or 'turnover' in field_names or 'sale_expected' in field_names:
+                invoice_types=['out_invoice','in_refund']
+            if 'purchase_avg_price' in field_names or 'purchase_num_invoiced' in field_names or 'total_cost' in field_names or 'normal_cost' in field_names:
+                invoice_types=['in_invoice','out_refund']
+            if len(invoice_types):
+                sql="""
+                select 
+                	avg(l.price_unit) as avg_unit_price,
+                	sum(l.quantity) as num_qty,
+                	sum(l.quantity * l.price_unit) as total,
+                	sum(sale_line.product_uom_qty * sale_line.price_unit) as sale_expected,
+                	sum(purchase_line.product_qty * purchase_line.price_unit) as normal_cost	
+                from account_invoice_line l
+                left join account_invoice i on (l.invoice_id = i.id)
+                left join sale_order_invoice_rel sale_invoice on (i.id=sale_invoice.invoice_id)
+                left join sale_order sale on sale.id=sale_invoice.order_id
+                left join sale_order_line  sale_line on sale.id=sale_line.order_id
+                left join purchase_order purchase on purchase.invoice_id=i.id
+                left join purchase_order_line purchase_line on purchase_line.order_id=purchase.id
+                where l.product_id = %s and i.state in ('%s') and i.type in ('%s')            
+                """%(val.id,"','".join(states),"','".join(invoice_types))                
+                cr.execute(sql)
+                result=cr.fetchall()[0]                
+                if 'sale_avg_price' in field_names or 'sale_num_invoiced' in field_names or 'turnover' in field_names or 'sale_expected' in field_names:
+                    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']
+                if 'purchase_avg_price' in field_names or 'purchase_num_invoiced' in field_names or 'total_cost' in field_names or 'normal_cost' in field_names:
+                    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']=val.turnover-val.total_cost
+            if 'expected_margin' in field_names:
+                res[val.id]['expected_margin']=val.sale_expected-val.normal_cost
+            if 'total_margin_rate' in field_names:
+                res[val.id]['total_margin_rate']=val.turnover and val.total_margin * 100 / val.turnover or 0.0
+            if 'expected_margin_rate' in field_names:
+                res[val.id]['expected_margin_rate']=val.sale_expected and val.expected_margin * 100 / val.sale_expected or 0.0 
+        return res
+    
+    _columns = {
+        'date_from': fields.function(_product_margin, method=True, type='date', string='From Date', multi=True),
+        'date_to': fields.function(_product_margin, method=True, type='date', string='To Date', multi=True),
+        'invoice_state': fields.function(_product_margin, method=True, type='selection', selection=[
+			('paid','Paid'),
+            ('open','All Open'),
+            ('draft_open','Draft and Open')
+			], string='Invoice State',multi=True, readonly=True),        
+        'sale_avg_price' : fields.function(_product_margin, method=True, type='float', string='Avg. Unit Price', multi='sale'),
+        'purchase_avg_price' : fields.function(_product_margin, method=True, type='float', string='Avg. Unit Price', multi='purchase'),
+        'sale_num_invoiced' : fields.function(_product_margin, method=True, type='float', string='# Invoiced', multi='sale'),
+        'purchase_num_invoiced' : fields.function(_product_margin, method=True, type='float', string='# Invoiced', multi='purchase'),
+        'sales_gap' : fields.function(_product_margin, method=True, type='float', string='Sales Gap', multi='sale'),
+        'purchase_gap' : fields.function(_product_margin, method=True, type='float', string='Purchase Gap', multi='purchase'),
+        'turnover' : fields.function(_product_margin, method=True, type='float', string='Turnover' ,multi='sale'),
+        'total_cost'  : fields.function(_product_margin, method=True, type='float', string='Total Cost', multi='purchase'),
+        'sale_expected' :  fields.function(_product_margin, method=True, type='float', string='Expected Sale', multi='sale'),
+        'normal_cost'  : fields.function(_product_margin, method=True, type='float', string='Normal Cost', multi='purchase'),
+        'total_margin' : fields.function(_product_margin, method=True, type='float', string='Total Margin', multi='total'),
+        'expected_margin' : fields.function(_product_margin, method=True, type='float', string='Expected Margin', multi='total'),
+        'total_margin_rate' : fields.function(_product_margin, method=True, type='float', string='Total Margin (%)', multi='margin'),
+        'expected_margin_rate' : fields.function(_product_margin, method=True, type='float', string='Expected Margin (%)', multi='margin'),
+    }   
+    
+    
+product_product()
+
+
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
+
diff --git a/addons/product_margin/product_margin_view.xml b/addons/product_margin/product_margin_view.xml
new file mode 100644
index 00000000000..d625c251dce
--- /dev/null
+++ b/addons/product_margin/product_margin_view.xml
@@ -0,0 +1,75 @@
+<?xml version="1.0" encoding="utf-8"?>
+<openerp>
+<data>
+
+	<wizard id="action_open_margin" menu="False" model="product.product" name="product.margin" string="Product Margin"/>	
+        
+    <menuitem id="menu_product_reporting" name="Reporting" parent ="product.menu_main_product" />
+        
+    <menuitem icon="STOCK_JUSTIFY_FILL" action="action_open_margin" id="menu_action_product_margin" type="wizard" sequence="5" parent="menu_product_reporting" />
+	
+	 <record id="view_product_margin_form" model="ir.ui.view">
+            <field name="name">product.margin.form.inherit</field>
+            <field name="model">product.product</field>
+            <field name="type">form</field>
+            <field name="inherit_id" ref="product.product_normal_form_view"/>
+            <field name="priority">10</field>
+            <field name="arch" type="xml">
+                <page string="Prices" position="after">
+                	<page string="Margin">
+	                	<separator string="Analysis Criteria" colspan="4"/>
+	                    <field name="date_from"/>
+	                    <field name="date_to"/>
+	                    <newline/>
+	                    <field name="invoice_state" />
+	                    
+	                    <separator string="Sales" colspan="4"/>
+	                    <field name="sale_avg_price"/>
+	                    <field name="list_price" string="Catalog Price" readonly="1"/>
+	                    <field name="sale_num_invoiced" />
+	     				 <field name="sales_gap" /> 
+	                    <field name="turnover" />
+	                    <field name="sale_expected" />
+	                    
+	                    <separator string="Purchases" colspan="4"/>
+	                    <field name="purchase_avg_price"/>
+	                    <field name="standard_price" string="Standard Price" readonly="1"/>
+	                    <field name="purchase_num_invoiced" />
+	                   <field name="purchase_gap" />
+	                   <field name="total_cost" />
+	                   <field name="normal_cost" />
+	                    
+	                    <separator string="Margins" colspan="4"/>
+	                    <field name="total_margin"/>
+	                    <field name="expected_margin"/>
+	                   <field name="total_margin_rate" widget="progressbar"/>
+	                    <field name="expected_margin_rate" widget="progressbar"/>
+	                   
+                </page>
+                </page>
+            </field>
+        </record>
+		
+		<record id="view_product_margin_tree" model="ir.ui.view">
+            <field name="name">product.margin.tree</field>
+            <field name="model">product.product</field>
+            <field name="type">tree</field>
+            <field name="arch" type="xml">
+                <tree string="Product Margin">
+                    <field name="name" select="1"/>
+                    <field name="default_code" select="1"/>
+                    <field name="sale_avg_price"/>
+                    <field name="sale_num_invoiced"/>
+                    <field name="turnover"/>
+                    <field name="sales_gap"/>
+                    <field name="total_cost"/>
+                    <field name="purchase_num_invoiced" string="#Purchased"/>
+                    <field name="total_margin"/>
+                    <field name="expected_margin"/>
+                    <field name="total_margin_rate"  widget="progressbar"/>
+            </tree>
+            </field>
+        </record>
+	
+</data>
+</openerp>
diff --git a/addons/product_margin/wizard/__init__.py b/addons/product_margin/wizard/__init__.py
new file mode 100644
index 00000000000..ed4ac23f486
--- /dev/null
+++ b/addons/product_margin/wizard/__init__.py
@@ -0,0 +1,33 @@
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+# Copyright (c) 2006 TINY SPRL. (http://tiny.be) All Rights Reserved.
+#                    Fabien Pinckaers <fp@tiny.Be>
+#
+# WARNING: This program as such is intended to be used by professional
+# programmers who take the whole responsability of assessing all potential
+# consequences resulting from its eventual inadequacies and bugs
+# End users who are looking for a ready-to-use solution with commercial
+# garantees and support are strongly adviced to contract a Free Software
+# Service Company
+#
+# This program is Free Software; you can redistribute it and/or
+# modify it under the terms of the GNU General Public License
+# as published by the Free Software Foundation; either version 2
+# of the License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
+#
+##############################################################################
+
+import wizard_product_margin
+
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
+
diff --git a/addons/product_margin/wizard/wizard_product_margin.py b/addons/product_margin/wizard/wizard_product_margin.py
new file mode 100644
index 00000000000..09df17da294
--- /dev/null
+++ b/addons/product_margin/wizard/wizard_product_margin.py
@@ -0,0 +1,85 @@
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+# Copyright (c) 2004-2008 TINY SPRL. (http://tiny.be) All Rights Reserved.
+#
+# $Id$
+#
+# WARNING: This program as such is intended to be used by professional
+# programmers who take the whole responsability of assessing all potential
+# consequences resulting from its eventual inadequacies and bugs
+# End users who are looking for a ready-to-use solution with commercial
+# garantees and support are strongly adviced to contract a Free Software
+# Service Company
+#
+# This program is Free Software; you can redistribute it and/or
+# modify it under the terms of the GNU General Public License
+# as published by the Free Software Foundation; either version 2
+# of the License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
+#
+##############################################################################
+
+import wizard
+import pooler
+import time
+
+def _action_open_window(self, cr, uid, data, context):
+    cr.execute('select id,name from ir_ui_view where name=%s and type=%s', ('product.margin.tree', 'tree'))
+    view_res = cr.fetchone()
+    return {
+        'name': 'Product Margin',
+         'context':{'date_from':data['form']['from_date'],'date_to':data['form']['to_date'],'invoice_state' : data['form']['invoice_state']},
+        'view_type': 'form',
+        "view_mode": 'tree,form',
+        'res_model':'product.product',
+        'type': 'ir.actions.act_window',
+        'view_id': view_res,
+        }
+    
+    
+class product_margin(wizard.interface):
+    form1 = '''<?xml version="1.0"?>
+    <form string="View Stock of Products">
+        <separator string="Select " colspan="4"/>
+        <field name="from_date"/>
+        <field name="to_date"/>
+        <field name="invoice_state"/>
+    </form>'''
+    form1_fields = {
+             'from_date': {
+                'string': 'From',
+                'type': 'date',
+        },
+             'to_date': {
+                'string': 'To',
+                'type': 'date',
+        },
+         'invoice_state': {
+                'string': 'Invoice State',
+                'type': 'selection',
+                'selection': [('paid','Paid'),('open','All Open'),('draft_open','Draft and Open')],
+                'required': True,
+                'default': lambda *a:"open",
+        },
+    }
+
+    states = {
+      'init': {
+            'actions': [],
+            'result': {'type': 'form', 'arch':form1, 'fields':form1_fields, 'state': [('end', 'Cancel','gtk-cancel'),('open', 'Open Margin','gtk-ok')]}
+        },
+    'open': {
+            'actions': [],
+            'result': {'type': 'action', 'action': _action_open_window, 'state':'end'}
+        }
+    }
+product_margin('product.margin')