Merge lp:~openbig/bigconsulting/order_point_calcultor into lp:bigconsulting

[gpa(OpenERP)]

improvement in order point calculator wizard bug 615927

[lp:~openbig/bigconsulting/order_point_calcultor]

81. By gpa(OpenERP) on 2010-08-16

optimization in stock order point wizard

82. By gpa(OpenERP) on 2010-08-16

improvement for automatically increment in scanned quantity and real quantity

83. By gpa(OpenERP) on 2010-08-17

changes in first condition of date

Unmerged revisions

=== modified file 'stock_minimum_calculator/stock_minimum_calculator_wizard.xml'
--- stock_minimum_calculator/stock_minimum_calculator_wizard.xml	2010-07-27 09:27:59 +0000
+++ stock_minimum_calculator/stock_minimum_calculator_wizard.xml	2010-08-16 12:42:42 +0000
@@ -9,7 +9,7 @@
             id="id_set_seasonal_trend"/>
      <wizard
             string="Order Point Cacluator"
-            model="product.product"
+            model="res.partner"
             name="orderpoint.calculator"
             menu="True"
             id="id_order_point_cacluator"/>

=== modified file 'stock_minimum_calculator/wizard/stock_order_point_calculator.py'
--- stock_minimum_calculator/wizard/stock_order_point_calculator.py	2010-08-04 13:12:02 +0000
+++ stock_minimum_calculator/wizard/stock_order_point_calculator.py	2010-08-16 12:42:42 +0000
@@ -34,13 +34,9 @@
     <field name="warehouse_id"/>
     <field name="location_id"/>
     <field name="seasonal_id"/>
+    <field name="max_qty_factor"/>
     <field name="delactive"/>
     <field name="method_qty_calculation" colspan="4"/>
-    <group attrs="{'invisible':[('method_qty_calculation','=','his_cons_without_product')]}" colspan="4">
-        <separator string="Configuration of Base Consumptions Periods" colspan="4"/>
-        <field name="date_start"/>
-        <field name="date_stop"/>
-    </group>
 </form>"""
 
 orderpoint_calculator_fields = {
@@ -70,24 +66,19 @@
         'required': True,
         'help':'Use this field as index. Reference index is Main Season with 1.00. If you want to change the orderpoint rules you are able to define new season with a seasonal index. This index should reference to 1.00. For example if you expect - 10% consumption for autumn season in comparition to main season enter 0.90.'
     },
-
-    'date_start': {
-        'string':'From Date',
-        'type':'date'
-    },
-
-    'date_stop': {
-        'string':'To Date',
-        'type':'date',
-        'default': lambda *a: time.strftime('%Y-%m-%d')
+    
+    'max_qty_factor': {
+        'string':'Max. Qty. Factor',
+        'type':'float',
+        'required': True
     },
 
     'method_qty_calculation':{
         'string':"Method Calculation",
         'type':'selection',
-        'selection':[('his_cons_with_product', 'Calculate with historical consumptions of products'),
+        'selection':[('his_cons_with_product', 'Calculate with planning values'),
                      ('his_cons_without_product', 'Calculate with Out historical consumptions of product'),
-                     ('hist_cons_and_open_sale', 'Calculate with historical consumptions of product and Open sales orders'),
+                     ('hist_cons_and_open_sale', 'Calculate with historical values'),
                     ],'required': True,
         'default': lambda *a:'his_cons_with_product'
     },
@@ -116,16 +107,99 @@
 
     return {'warehouse_id': warehouse_id, 'location_id':location_id,'date_start':from_date,'seasonal_id':seasonal_id}
 
+
+def _sale_consume_qty(self, cr, uid, product_id, from_date, to_date, state, context):
+    """calculation of consumption of product from_date to to_date , where state is done """
+    
+    pool = pooler.get_pool(cr.dbname)
+    
+    sale_obj = pool.get('sale.order')
+    sale_line_obj = pool.get("sale.order.line")
+    consum_qty = 0.0
+    sale_ids = sale_obj.search(cr,uid,[('date_order','>=',from_date),('date_order','<=',to_date), ('state','=',state)])
+    if sale_ids:
+        for sid in sale_ids:
+            sale_line_id = sale_line_obj.search(cr,uid,[('order_id','=',sid),('product_id','=',product_id)])
+            for line_id in sale_line_id:
+                sale_line_data= sale_line_obj.browse(cr, uid, line_id, context=context).product_uom_qty
+                consum_qty += sale_line_data
+
+    return consum_qty
+    
+def _sale_open_qty(self, cr, uid, product_id, state, context):
+    """Calculation of open sale orders"""
+    
+    pool = pooler.get_pool(cr.dbname)
+    
+    sale_obj = pool.get('sale.order')
+    sale_line_obj = pool.get("sale.order.line")
+    open_sale_qty = 0.0
+    open_sale_ids = sale_obj.search(cr, uid, [('state','in',state)])
+    if open_sale_ids:
+        for sale_id in open_sale_ids:
+            sale_open_line_ids = sale_line_obj.search(cr,uid,[('order_id','=',sale_id),('product_id','=',product_id)])
+            for open_line_id in sale_open_line_ids:
+                sale_line_data= sale_line_obj.browse(cr,uid,open_line_id)
+                open_sale_qty += sale_line_data.product_uom_qty
+
+    return open_sale_qty
+
+def _purchase_open_qty(self, cr, uid, product_id, state, context):
+    """ Calculation of open purchase orders """
+    
+    pool = pooler.get_pool(cr.dbname)
+    
+    purchase_obj = pool.get('purchase.order')
+    purchase_order_line_obj = pool.get('purchase.order.line')
+    open_purchase_qty = 0.0
+    open_purchase_ids = purchase_obj.search(cr,uid,[('state','=',state)])
+    if open_purchase_ids:
+        for purchase_id in open_purchase_ids:
+            open_purchase_line_ids= purchase_order_line_obj.search(cr,uid,[('order_id','=',purchase_id),('product_id','=',product_id)])
+            for open_pur_id in open_purchase_line_ids:
+                purchase_line_data = purchase_order_line_obj.browse(cr, uid, open_pur_id, context=context)
+                open_purchase_qty += purchase_line_data.product_qty
+                 
+    return open_purchase_qty
+
+def _refund_qty(self, cr, uid, product_id, from_date, to_date, state, context):
+    """ Calculation of the refund quantities of the prduct from the refund invoice, between from_date, to_date"""
+    
+    pool = pooler.get_pool(cr.dbname)
+    
+    account_invoice_obj = pool.get('account.invoice')
+    account_invoice_line_obj = pool.get('account.invoice.line') 
+    refund_qty = 0.0
+    refund_invoice_ids = account_invoice_obj.search(cr,uid,[('type','in',state),('date_invoice','>=',from_date),('date_invoice','<=',to_date)])
+    for refund_id in refund_invoice_ids:
+        invoie_line_ids = account_invoice_line_obj.search(cr,uid,[('invoice_id','=',refund_id),('product_id','=',product_id)])
+        for invoice_line_id in invoie_line_ids:
+            inovice_line_data = account_invoice_line_obj.browse(cr, uid, invoice_line_id, context=context).quantity
+            refund_qty += inovice_line_data
+
+    return refund_qty
+
+
+def _date_diff(self, cr, uid, product_id, from_date, to_date, context):
+    """ Date difference between from_date and to_date """
+    
+    first_date = time.mktime(time.strptime(from_date,'%Y-%m-%d %H:%M:%S'))
+    last_date = time.mktime(time.strptime(to_date,'%Y-%m-%d %H:%M:%S'))
+    diff_day = (last_date-first_date)/(3600*24)  
+    return diff_day
+    
 def _do_calculate(self, cr, uid, data, context):
         pool = pooler.get_pool(cr.dbname)
 
         purchase_obj = pool.get('purchase.order')
-        sale_obj = pool.get('sale.order')
+        purchase_order_line_obj = pool.get('purchase.order.line')
         product_obj = pool.get('product.product')
         supp_obj = pool.get('product.supplierinfo')
         supp_cost_opt_obj = pool.get('supplier.cost.opti.lead.time')
         min_rule_obj = pool.get('stock.warehouse.orderpoint')
         seasonal_obj = pool.get('product.seasonal')
+        stock_move_obj = pool.get('stock.move')
+         
         ##### Calculation of purchase lead time
         purchase_lead_time = 0.0
         company = pool.get('res.users').browse(cr, uid, uid, context).company_id
@@ -135,117 +209,143 @@
         seasonal_id = data['form']['seasonal_id']
         seasonal_data = seasonal_obj.browse(cr, uid, seasonal_id)
         seasonal_factor = seasonal_data.seasonal_factor
-
-        ##### Calculation of supplier lead time
-
-        for id in data['ids']:
-            product_id = product_obj.browse(cr, uid, id, context)
-            supplier_lead_time = 0.0
-            if product_id:
-                for supp in product_id.seller_ids:
-                    sup = supp_obj.browse(cr, uid, supp.id, context)
-                    supplier_lead_time = sup.delay
-
-            ###### Calculation of average_daily_consumption
-            date_start = data['form']['date_start']
-            date_stop = data['form']['date_stop']
-            average_daily_consumption = 0.0
-            sale_line_obj = pool.get("sale.order.line")
-            consum_qty = 0.0
-            sale_ids = sale_obj.search(cr,uid,[('date_order','>=',date_start),('date_order','<=',date_stop)])
-            if sale_ids:
-                for sid in sale_ids:
-                    sale_line_id = sale_line_obj.search(cr,uid,[('order_id','=',sid),('product_id','=',product_id.id)])
-                    for line_id in sale_line_id:
-                        sale_line_data= sale_line_obj.browse(cr,uid,line_id)
-                        consum_qty += sale_line_data.product_uom_qty
-                       ###here consume quantity is total of selected product
-            ##################calculation of date difference between first and last sale order
-            sale_ids = sale_obj.search(cr,uid,[('date_order','>=',date_start),('date_order','<=',date_stop)], order='date_order asc')
-            if sale_ids:
-                start_sale_id = sale_ids[0]
-                last_sale_id =  sale_ids[-1]
-                first_sale_date = sale_obj.browse(cr,uid,start_sale_id).date_order
-                last_sale_date = sale_obj.browse(cr,uid,last_sale_id).date_order
-                first_date = time.mktime(time.strptime(first_sale_date,'%Y-%m-%d'))
-                last_date = time.mktime(time.strptime(last_sale_date,'%Y-%m-%d'))
-                diff_day = (last_date-first_date)/(3600*24)
-                if diff_day > 0:
-                    average_daily_consumption = consum_qty / diff_day
-            ###### Calculation of plan_average_daily_consumption
-            plan_average_daily_consumption = 0.0
-            if product_id.plan_avg_consume:
-                plan_average_daily_consumption = product_id.plan_avg_consume
-
-            ####### get location #####
-            location_id = data['form']['location_id']
-
-            ########## calculation historical consumption of products and open sales orders“
-            ### total sale qty with open state
-
-            sale_qty = 0.0
-            open_sale_ids = sale_obj.search(cr, uid, [('state','in',['manual','progress'])])
-            if sale_ids:
-                for sale_id in open_sale_ids:
-                    sale_open_line_ids= sale_line_obj.search(cr,uid,[('order_id','=',sale_id),('product_id','=',product_id.id)])
-                    for open_line_id in sale_open_line_ids:
-                        sale_line_data= sale_line_obj.browse(cr,uid,open_line_id)
-                        sale_qty += sale_line_data.product_uom_qty
-            ###################
-            purchase_ids = purchase_obj.search(cr,uid,[])
-            if purchase_ids:
-                start_pur_id = min(purchase_ids)
-                last_pur_id =  max(purchase_ids)
-
-            ##################calculation of date difference between first and last purchase order
-                first_purchase_date = purchase_obj.browse(cr,uid,start_pur_id).date_order
-                last_purchase_date = purchase_obj.browse(cr,uid,last_pur_id).date_order
-                first_date = time.mktime(time.strptime(first_purchase_date,'%Y-%m-%d'))
-                last_date = time.mktime(time.strptime(last_purchase_date,'%Y-%m-%d'))
-                pur_diff_day = (last_date-first_date)/(3600*24)
-            product_max_qty = 0.0
-            product_min_qty = 0.0
-
-            if data['form']['method_qty_calculation'] == 'his_cons_with_product':
-                product_min_qty = int(math.ceil(average_daily_consumption *  (supplier_lead_time + purchase_lead_time) * seasonal_factor))
-                product_max_qty = 2 * product_min_qty
-
-            elif data['form']['method_qty_calculation'] == 'his_cons_without_product':
-                product_min_qty =  int(math.ceil(plan_average_daily_consumption * (supplier_lead_time + purchase_lead_time) * seasonal_factor))
-                product_max_qty = 2 * product_min_qty
-
-            else:
-                try:
-                    product_min_qty =  int(math.ceil((consum_qty + sale_qty) / pur_diff_day * (supplier_lead_time + purchase_lead_time) * seasonal_factor))
-                except Exception,e:
-                     product_min_qty = 0.0
-                product_max_qty = 2 * product_min_qty
-
-            if data['form']['delactive']:
-                stock_rule_ids = min_rule_obj.search(cr,uid,[('warehouse_id','=',data['form']['warehouse_id']),('product_id','=',product_id.id)])
-                for stock_rule_id in stock_rule_ids:
-                    min_rule_obj.unlink(cr, uid, [stock_rule_id],context=context)
-
-            mini_stock_rule_id = min_rule_obj.search(cr,uid,[('warehouse_id','=',data['form']['warehouse_id']),('product_id','=',product_id.id)])
-            if mini_stock_rule_id:
-                for stock_rule_id in mini_stock_rule_id:
-                    min_rule_obj.write(cr, uid, [stock_rule_id],{'product_min_qty':product_min_qty,'product_max_qty':product_max_qty,},context=context)
-            else:
-                name =  pool.get('ir.sequence').get(cr, uid, 'stock.orderpoint.calculator')
-                pool.get('stock.warehouse.orderpoint').create(cr, uid, {
-                                'name': name,
-                                'active': True,
-                                'warehouse_id': data['form']['warehouse_id'],
-                                'location_id': location_id,
-                                'product_id': id,
-                                'product_min_qty': product_min_qty,
-                                'product_max_qty': product_max_qty,
-                                'qty_multiple':1,
-                                'product_uom':product_id.uom_id.id,
-                                'logic':'max'
-                            } )
-
-
+        
+        # max_qty_factor factor
+        max_qty_factor = data['form']['max_qty_factor']
+        
+        # 1. Calculation of the date date start in one year later than current date
+        from_date = (datetime.now() - relativedelta(months=12)).strftime('%Y-%m-%d %H:%M:%S')
+        to_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
+        
+        # 2.1 first find the all the product which fullfill the criteria of the 2.1
+        product_ids = product_obj.search(cr,uid,[('active','=',True),('type','=','product'),
+                                                 ('procure_method','=','make_to_stock'),
+                                                 ('supply_method','=','buy'),
+                                                 ('purchase_ok','=',True),
+                                                 ('state','not in',['end','obsolete']),
+                                                 ])
+        ####### get location #####
+        location_id = data['form']['location_id']
+        ###########
+        
+        #2. Second point find the products for the perticular suppliers 
+        for partner_id in data['ids']:
+            filter_products = []
+            for product_id in product_ids:
+                product_data = product_obj.browse(cr, uid, product_id, context=context)
+                for seller_id in product_data.seller_ids:
+                    if seller_id.name.id == partner_id:
+                        filter_products.append(product_id)
+                        
+            #3. Calculation of the first stock input date as first input
+            for filter_product_id in filter_products:
+                product_data = pool.get('product.product').browse(cr, uid, filter_product_id, context=context)
+                supplier_lead_time = 0.0
+                
+                # supplier_lead_time of the supplier delay
+                for seller_id in product_data.seller_ids:
+                    if seller_id.name.id == partner_id:
+                        supplier_lead_time = seller_id.delay
+                        
+                stock_move_ids = stock_move_obj.search(cr, uid, [('product_id','=',filter_product_id)])
+
+                average_daily_consumption = 0.0 
+                first_stock_move_ids = []
+                # Take the first input date of the stock move where stock move of incoming picking or stock move not have any picking 
+                for first_stock_id in stock_move_ids:
+                    first_data = stock_move_obj.browse(cr, uid, first_stock_id, context=context)
+                    if first_data.picking_id:
+                       if first_data.picking_id.type == 'in':
+                           first_stock_move_ids.append(first_stock_id)
+                    else:
+                       first_stock_move_ids.append(first_stock_id)
+
+                if first_stock_move_ids:
+                    first_stock_move_id = first_stock_move_ids[0]
+                    first_input = stock_move_obj.browse(cr, uid, first_stock_move_id, context=context).date_planned
+
+                    # Case 1 if first input is less than from date
+                    if first_input < from_date:
+                        consum_qty = _sale_consume_qty(self, cr, uid, from_date, to_date, 'done', context)
+                        refund_qty = _refund_qty(self, cr, uid, filter_product_id, from_date, to_date, ['out_refund','in_refund'], context)
+                        open_sale_qty = _sale_open_qty(self, cr, uid, filter_product_id, from_date, to_date, ['manual','progress'], context) 
+                        open_purchase_qty = _purchase_open_qty(self, cr, uid, filter_product_id, 'approved', context) 
+                        pur_diff_day = _date_diff(self, cr, uid, filter_product_id, from_date, to_date, context)                       
+                        ### calculation of  average_daily_consumption
+                        if pur_diff_day > 0:
+                            average_daily_consumption = (consum_qty - refund_qty + open_sale_qty - open_purchase_qty) / pur_diff_day
+                        
+                    elif from_date < first_input and  first_input < to_date:
+                        first_date = first_input
+                        consum_qty = _sale_consume_qty(self, cr, uid, filter_product_id, first_date, to_date, 'done', context)
+                        refund_qty = _refund_qty(self, cr, uid, filter_product_id, first_date, to_date, ['out_refund','in_refund'], context)
+                        open_sale_qty = _sale_open_qty(self, cr, uid, filter_product_id, ['manual','progress'], context)
+                        open_purchase_qty = _purchase_open_qty(self, cr, uid, filter_product_id, 'approved', context)
+                        pur_diff_day = _date_diff(self, cr, uid, filter_product_id, first_date, to_date, context)
+                        
+                        ### calculation of  average_daily_consumption                                           
+                        if pur_diff_day>0:
+                            average_daily_consumption = (consum_qty - refund_qty + open_sale_qty - open_purchase_qty) / pur_diff_day
+                else:
+                    open_sale_qty = _sale_open_qty(self, cr, uid, filter_product_id, ['manual','progress'], context)
+                    open_purchase_qty =  _purchase_open_qty(self, cr, uid, filter_product_id, 'approved', context)
+                    
+                    # calculation of first open purchase order date   
+                    open_purchase_ids = purchase_obj.search(cr,uid,[('state','=','approved')])  
+                    first_open_purchase_order = min(open_purchase_ids)
+                    fst_open_pur_date = purchase_obj.browse(cr, uid, first_open_purchase_order, context=context).date_order
+                            
+                    # Days difference between to date and first purchase date
+                    pur_to_date = time.mktime(time.strptime(to_date,'%Y-%m-%d %H:%M:%S'))
+                    pur_open_date = time.mktime(time.strptime(fst_open_pur_date,'%Y-%m-%d'))
+                    pur_diff_day = (pur_to_date-pur_open_date)/(3600*24)
+                    
+                    if pur_diff_day>0:
+                        average_daily_consumption = (open_sale_qty - open_purchase_qty)/pur_diff_day
+                    
+                ###### Calculation of plan_average_daily_consumption
+                plan_average_daily_consumption = 0.0
+                plan_average_daily_consumption = product_data.plan_avg_consume
+                        
+                if data['form']['method_qty_calculation'] == 'his_cons_with_product':
+                    product_min_qty = int(math.ceil(average_daily_consumption *  (supplier_lead_time + purchase_lead_time) * seasonal_factor))
+                    product_max_qty = max_qty_factor * product_min_qty
+    
+                elif data['form']['method_qty_calculation'] == 'his_cons_without_product':
+                    product_min_qty =  int(math.ceil(plan_average_daily_consumption * (supplier_lead_time + purchase_lead_time) * seasonal_factor))
+                    product_max_qty = max_qty_factor * product_min_qty
+    
+                else:
+                    try:
+                        product_min_qty =  int(math.ceil((consum_qty + open_sale_qty) / pur_diff_day * (supplier_lead_time + purchase_lead_time) * seasonal_factor))
+                    except Exception,e:
+                         product_min_qty = 0.0
+                    product_max_qty = max_qty_factor * product_min_qty
+                    
+                if data['form']['delactive']:
+                    stock_rule_ids = min_rule_obj.search(cr,uid,[('warehouse_id','=',data['form']['warehouse_id']),('product_id','=',filter_product_id)])
+                    for stock_rule_id in stock_rule_ids:
+                        min_rule_obj.unlink(cr, uid, [stock_rule_id],context=context)
+    
+                mini_stock_rule_id = min_rule_obj.search(cr,uid,[('warehouse_id','=',data['form']['warehouse_id']),('product_id','=',filter_product_id)])
+                if mini_stock_rule_id:
+                    for stock_rule_id in mini_stock_rule_id:
+                        min_rule_obj.write(cr, uid, [stock_rule_id],{'product_min_qty':product_min_qty,'product_max_qty':product_max_qty,},context=context)
+                else:
+                    name =  pool.get('ir.sequence').get(cr, uid, 'stock.orderpoint.calculator')
+                    pool.get('stock.warehouse.orderpoint').create(cr, uid, {
+                                    'name': name,
+                                    'active': True,
+                                    'warehouse_id': data['form']['warehouse_id'],
+                                    'location_id': location_id,
+                                    'product_id': filter_product_id,
+                                    'product_min_qty': product_min_qty,
+                                    'product_max_qty': product_max_qty,
+                                    'qty_multiple':1,
+                                    'product_uom':product_data.uom_id.id,
+                                    'logic':'price',
+                                } )
+                                           
         return {}
 
 def _get_message(self, cr, uid, data, context):