addons/stock_planning/stock_planning.py

   1 # -*- coding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
   6 #
   7 #    This program is free software: you can redistribute it and/or modify
   8 #    it under the terms of the GNU Affero General Public License as
   9 #    published by the Free Software Foundation, either version 3 of the
  10 #    License, or (at your option) any later version.
  11 #
  12 #    This program is distributed in the hope that it will be useful,
  13 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15 #    GNU Affero General Public License for more details.
  16 #
  17 #    You should have received a copy of the GNU Affero General Public License
  18 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  19 #
  20 ##############################################################################
  21
  22 import time
  23 from datetime import datetime
  24 from dateutil.relativedelta import relativedelta
  25
  26 from osv import osv, fields
  27 import netsvc
  28 from tools.translate import _
  29
  30 def rounding(fl, round_value):
  31     if not round_value:
  32         return fl
  33     return round(fl / round_value) * round_value
  34
  35 # Periods have no company_id field as they can be shared across similar companies.
  36 # If somone thinks different it can be improved.
  37 class stock_period(osv.osv):
  38     _name = "stock.period"
  39     _description = "stock period"
  40     _columns = {
  41         'name': fields.char('Period Name', size=64, required=True),
  42         'date_start': fields.datetime('Start Date', required=True),
  43         'date_stop': fields.datetime('End Date', required=True),
  44         'state': fields.selection([('draft','Draft'), ('open','Open'),('close','Close')], 'State')
  45     }
  46     _defaults = {
  47         'state': 'draft'
  48     }
  49
  50     def button_open(self, cr, uid, ids, context):
  51         self.write(cr, uid, ids, {'state': 'open'})
  52         return True
  53
  54     def button_close(self, cr, uid, ids, context):
  55         self.write(cr, uid, ids, {'state': 'close'})
  56         return True
  57
  58 stock_period()
  59
  60 # Stock and Sales Forecast object. Previously stock_planning_sale_prevision.
  61 # A lot of changes in 1.1
  62 class stock_sale_forecast(osv.osv):
  63     _name = "stock.sale.forecast"
  64
  65     _columns = {
  66         'company_id':fields.many2one('res.company', 'Company', required=True),
  67         'create_uid': fields.many2one('res.users', 'Responsible'),
  68         'name': fields.char('Name', size=64, readonly=True, states={'draft': [('readonly',False)]}),
  69         'user_id': fields.many2one('res.users', 'Created/Validated by',readonly=True, \
  70                                         help='Shows who created this forecast, or who validated.'),
  71         'warehouse_id': fields.many2one('stock.warehouse', 'Warehouse', required=True, readonly=True, states={'draft': [('readonly',False)]}, \
  72                                         help='Shows which warehouse this forecast concerns. '\
  73                                          'If during stock planning you will need sales forecast for all warehouses choose any warehouse now.'),
  74         'period_id': fields.many2one('stock.period', 'Period', required=True, readonly=True, states={'draft':[('readonly',False)]}, \
  75                                         help = 'Shows which period this forecast concerns.'),
  76         'product_id': fields.many2one('product.product', 'Product', readonly=True, required=True, states={'draft':[('readonly',False)]}, \
  77                                         help = 'Shows which product this forecast concerns.'),
  78         'product_qty': fields.float('Product Quantity', required=True, readonly=True, states={'draft':[('readonly',False)]}, \
  79                                         help= 'Forecasted quantity.'),
  80         'product_amt': fields.float('Product Amount', readonly=True, states={'draft':[('readonly',False)]}, \
  81                                         help='Forecast value which will be converted to Product Quantity according to prices.'),
  82         'product_uom_categ': fields.many2one('product.uom.categ', 'Product UoM Category'),  # Invisible field for product_uom domain
  83         'product_uom': fields.many2one('product.uom', 'Product UoM', required=True, readonly=True, states={'draft':[('readonly',False)]}, \
  84                         help = "Unit of Measure used to show the quanities of stock calculation." \
  85                         "You can use units form default category or from second category (UoS category)."),
  86         'product_uos_categ' : fields.many2one('product.uom.categ', 'Product UoS Category'), # Invisible field for product_uos domain
  87 # Field used in onchange_uom to check what uom was before change and recalculate quantities acording to old uom (active_uom) and new uom.
  88         'active_uom': fields.many2one('product.uom',  string = "Active UoM"),
  89         'state': fields.selection([('draft','Draft'),('validated','Validated')],'State',readonly=True),
  90         'analyzed_period1_id': fields.many2one('stock.period', 'Period1', readonly=True, states={'draft':[('readonly',False)]},),
  91         'analyzed_period2_id': fields.many2one('stock.period', 'Period2', readonly=True, states={'draft':[('readonly',False)]},),
  92         'analyzed_period3_id': fields.many2one('stock.period', 'Period3', readonly=True, states={'draft':[('readonly',False)]},),
  93         'analyzed_period4_id': fields.many2one('stock.period', 'Period4', readonly=True, states={'draft':[('readonly',False)]},),
  94         'analyzed_period5_id': fields.many2one('stock.period' , 'Period5', readonly=True, states={'draft':[('readonly',False)]},),
  95         'analyzed_user_id': fields.many2one('res.users', 'This User', required=False, readonly=True, states={'draft':[('readonly',False)]},),
  96         'analyzed_dept_id': fields.many2one('hr.department', 'This Department', required=False, \
  97                                     readonly=True, states={'draft':[('readonly',False)]},),
  98         'analyzed_warehouse_id': fields.many2one('stock.warehouse' , 'This Warehouse', required=False, \
  99                                     readonly=True, states={'draft':[('readonly',False)]}),
 100         'analyze_company': fields.boolean('Per Company', readonly=True, states={'draft':[('readonly',False)]}, \
 101                                     help = "Check this box to see the sales for whole company."),
 102         'analyzed_period1_per_user': fields.float('This User Period1', readonly=True),
 103         'analyzed_period2_per_user': fields.float('This User Period2', readonly=True),
 104         'analyzed_period3_per_user': fields.float('This User Period3', readonly=True),
 105         'analyzed_period4_per_user': fields.float('This User Period4', readonly=True),
 106         'analyzed_period5_per_user': fields.float('This User Period5', readonly=True),
 107         'analyzed_period1_per_dept': fields.float('This Dept Period1', readonly=True),
 108         'analyzed_period2_per_dept': fields.float('This Dept Period2', readonly=True),
 109         'analyzed_period3_per_dept': fields.float('This Dept Period3', readonly=True),
 110         'analyzed_period4_per_dept': fields.float('This Dept Period4', readonly=True),
 111         'analyzed_period5_per_dept': fields.float('This Dept Period5', readonly=True),
 112         'analyzed_period1_per_warehouse': fields.float('This Warehouse Period1', readonly=True),
 113         'analyzed_period2_per_warehouse': fields.float('This Warehouse Period2', readonly=True),
 114         'analyzed_period3_per_warehouse': fields.float('This Warehouse Period3', readonly=True),
 115         'analyzed_period4_per_warehouse': fields.float('This Warehouse Period4', readonly=True),
 116         'analyzed_period5_per_warehouse': fields.float('This Warehouse Period5', readonly=True),
 117         'analyzed_period1_per_company': fields.float('This Copmany Period1', readonly=True),
 118         'analyzed_period2_per_company': fields.float('This Company Period2', readonly=True),
 119         'analyzed_period3_per_company': fields.float('This Company Period3', readonly=True),
 120         'analyzed_period4_per_company': fields.float('This Company Period4', readonly=True),
 121         'analyzed_period5_per_company': fields.float('This Company Period5', readonly=True),
 122     }
 123     _defaults = {
 124         'user_id': lambda obj, cr, uid, context: uid,
 125         'state': 'draft',
 126         'company_id': lambda self,cr,uid,c: self.pool.get('res.company')._company_default_get(cr, uid, 'stock.sale.forecast', context=c),
 127     }
 128
 129     def action_validate(self, cr, uid, ids, *args):
 130         self.write(cr, uid, ids, {'state': 'validated','user_id': uid})
 131         return True
 132
 133     def unlink(self, cr, uid, ids, context=None):
 134         forecasts = self.read(cr, uid, ids, ['state'])
 135         unlink_ids = []
 136         for t in forecasts:
 137             if t['state'] in ('draft'):
 138                 unlink_ids.append(t['id'])
 139             else:
 140                 raise osv.except_osv(_('Invalid action !'), _('Cannot delete Validated Sale Forecasts !'))
 141         osv.osv.unlink(self, cr, uid, unlink_ids, context=context)
 142         return True
 143
 144     def onchange_company(self, cr, uid, ids, company_id=False):
 145         result = {}
 146         if not company_id:
 147             return result
 148         result['warehouse_id'] = False
 149         result['analyzed_user_id'] = False
 150         result['analyzed_dept_id'] = False
 151         result['analyzed_warehouse_id'] = False
 152         return {'value': result}
 153
 154     def product_id_change(self, cr, uid, ids, product_id=False):
 155         ret = {}
 156         if product_id:
 157             product_rec =  self.pool.get('product.product').browse(cr, uid, product_id)
 158             ret['product_uom'] = product_rec.uom_id.id
 159             ret['product_uom_categ'] = product_rec.uom_id.category_id.id
 160             ret['product_uos_categ'] = product_rec.uos_id and product_rec.uos_id.category_id.id or False
 161             ret['active_uom'] = product_rec.uom_id.id
 162         else:
 163             ret['product_uom'] = False
 164             ret['product_uom_categ'] = False
 165             ret['product_uos_categ'] = False
 166         res = {'value': ret}
 167         return res
 168
 169     def onchange_uom(self, cr, uid, ids, product_uom=False, product_qty=0.0,
 170                      active_uom=False, product_id=False):
 171         ret = {}
 172         if product_uom and product_id:
 173             coeff_uom2def = self._to_default_uom_factor(cr, uid, product_id, active_uom, {})
 174             coeff_def2uom, round_value = self._from_default_uom_factor(cr, uid, product_id, product_uom, {})
 175             coeff = coeff_uom2def * coeff_def2uom
 176             ret['product_qty'] = rounding(coeff * product_qty, round_value)
 177             ret['active_uom'] = product_uom
 178         return {'value': ret}
 179
 180     def product_amt_change(self, cr, uid, ids, product_amt=0.0, product_uom=False, product_id=False):
 181         round_value = 1
 182         qty = 0.0
 183         if product_amt and product_id:
 184             product = self.pool.get('product.product').browse(cr, uid, product_id)
 185             coeff_def2uom = 1
 186             if (product_uom != product.uom_id.id):
 187                 coeff_def2uom, round_value = self._from_default_uom_factor(cr, uid, product_id, product_uom, {})
 188             qty = rounding(coeff_def2uom * product_amt/(product.product_tmpl_id.list_price), round_value)
 189         res = {'value': {'product_qty': qty}}
 190         return res
 191
 192     def _to_default_uom_factor(self, cr, uid, product_id, uom_id, context=None):
 193         uom_obj = self.pool.get('product.uom')
 194         product_obj = self.pool.get('product.product')
 195         product = product_obj.browse(cr, uid, product_id)
 196         uom = uom_obj.browse(cr, uid, uom_id, context=context)
 197         coef = uom.factor
 198         if uom.category_id.id <> product.uom_id.category_id.id:
 199             coef = coef / product.uos_coeff
 200         return product.uom_id.factor / coef
 201
 202     def _from_default_uom_factor(self, cr, uid, product_id, uom_id, context):
 203         uom_obj = self.pool.get('product.uom')
 204         product_obj = self.pool.get('product.product')
 205         product = product_obj.browse(cr, uid, product_id)
 206         uom = uom_obj.browse(cr, uid, uom_id, context=context)
 207         res = uom.factor
 208         if uom.category_id.id <> product.uom_id.category_id.id:
 209             res = res / product.uos_coeff
 210         return res / product.uom_id.factor, uom.rounding
 211
 212     def _sales_per_users(self, cr, uid, so, so_line, company, users):
 213         cr.execute("SELECT sum(sol.product_uom_qty) FROM sale_order_line AS sol LEFT JOIN sale_order AS s ON (s.id = sol.order_id) " \
 214                    "WHERE (sol.id IN %s) AND (s.state NOT IN (\'draft\',\'cancel\')) AND (s.id IN %s) AND (s.company_id=%s) " \
 215                     "AND (s.user_id IN %s) " ,(tuple(so_line), tuple(so), company, tuple(users)))
 216         ret = cr.fetchone()[0] or 0.0
 217         return ret
 218
 219     def _sales_per_warehouse(self, cr, uid, so, so_line, company, shops):
 220         cr.execute("SELECT sum(sol.product_uom_qty) FROM sale_order_line AS sol LEFT JOIN sale_order AS s ON (s.id = sol.order_id) " \
 221                    "WHERE (sol.id IN %s) AND (s.state NOT IN (\'draft\',\'cancel\')) AND (s.id IN %s)AND (s.company_id=%s) " \
 222                     "AND (s.shop_id IN %s)" ,(tuple(so_line), tuple(so), company, tuple(shops)))
 223         ret = cr.fetchone()[0] or 0.0
 224         return ret
 225
 226     def _sales_per_company(self, cr, uid, so, so_line, company):
 227         cr.execute("SELECT sum(sol.product_uom_qty) FROM sale_order_line AS sol LEFT JOIN sale_order AS s ON (s.id = sol.order_id) " \
 228                    "WHERE (sol.id IN %s) AND (s.state NOT IN (\'draft\',\'cancel\')) AND (s.id IN %s) AND (s.company_id=%s)", (tuple(so_line), tuple(so), company))
 229         ret = cr.fetchone()[0] or 0.0
 230         return ret
 231
 232     def calculate_sales_history(self, cr, uid, ids, context, *args):
 233         sales = [[0,0,0,0],[0,0,0,0],[0,0,0,0],[0,0,0,0],[0,0,0,0],]
 234         for obj in self.browse(cr, uid, ids):
 235             periods = obj.analyzed_period1_id, obj.analyzed_period2_id, obj.analyzed_period3_id, obj.analyzed_period4_id, obj.analyzed_period5_id
 236             so_obj = self.pool.get('sale.order')
 237             so_line_obj = self.pool.get('sale.order.line')
 238             so_line_product_ids = so_line_obj.search(cr, uid, [('product_id','=', obj.product_id.id)], context = context)
 239             if so_line_product_ids:
 240                 so_line_product_set = ','.join(map(str,so_line_product_ids))
 241                 if obj.analyzed_warehouse_id:
 242                     shops = self.pool.get('sale.shop').search(cr, uid,[('warehouse_id','=', obj.analyzed_warehouse_id.id)], context = context)
 243                     shops_set = ','.join(map(str,shops))
 244                 else:
 245                     shops = False
 246                 if obj.analyzed_dept_id:
 247                     dept_obj = self.pool.get('hr.department')
 248                     dept_id =  obj.analyzed_dept_id.id and [obj.analyzed_dept_id.id] or []
 249                     dept_ids = dept_obj.search(cr,uid,[('parent_id','child_of',dept_id)])
 250                     cr.execute("SELECT user_id FROM resource_resource rsc, hr_employee emp WHERE (emp.resource_id = rsc.id and emp.department_id IN %s)" ,(tuple(dept_ids),))
 251                     dept_users = [x for x, in cr.fetchall()]
 252                     dept_users_set =  map(str,dept_users)
 253                 else:
 254                     dept_users = False
 255                 factor, round_value = self._from_default_uom_factor(cr, uid, obj.product_id.id, obj.product_uom.id, context=context)
 256                 for i, period in enumerate(periods):
 257                     if period:
 258                         so_period_ids = so_obj.search(cr, uid, [('date_order','>=',period.date_start),('date_order','<=',period.date_stop) ], context = context)
 259                         if so_period_ids:
 260                             if obj.analyzed_user_id:
 261                                 user_set = str(obj.analyzed_user_id.id)
 262                                 sales[i][0] = self._sales_per_users(cr, uid, so_period_ids, so_line_product_ids, obj.company_id.id, user_set)
 263                                 sales[i][0] *= factor
 264                             if dept_users:
 265                                 sales[i][1] = self._sales_per_users(cr, uid, so_period_ids,  so_line_product_ids, obj.company_id.id, dept_users_set)
 266                                 sales[i][1] *= factor
 267                             if shops:
 268                                 sales[i][2] = self._sales_per_warehouse(cr, uid, so_period_ids,  so_line_product_ids, obj.company_id.id, shops_set)
 269                                 sales[i][2] *= factor
 270                             if obj.analyze_company:
 271                                 sales[i][3] = self._sales_per_company(cr, uid, so_period_ids, so_line_product_ids, obj.company_id.id, )
 272                                 sales[i][3] *= factor
 273         self.write(cr, uid, ids, {
 274             'analyzed_period1_per_user': sales[0][0],
 275             'analyzed_period2_per_user': sales[1][0],
 276             'analyzed_period3_per_user': sales[2][0],
 277             'analyzed_period4_per_user': sales[3][0],
 278             'analyzed_period5_per_user': sales[4][0],
 279             'analyzed_period1_per_dept': sales[0][1],
 280             'analyzed_period2_per_dept': sales[1][1],
 281             'analyzed_period3_per_dept': sales[2][1],
 282             'analyzed_period4_per_dept': sales[3][1],
 283             'analyzed_period5_per_dept': sales[4][1],
 284             'analyzed_period1_per_warehouse': sales[0][2],
 285             'analyzed_period2_per_warehouse': sales[1][2],
 286             'analyzed_period3_per_warehouse': sales[2][2],
 287             'analyzed_period4_per_warehouse': sales[3][2],
 288             'analyzed_period5_per_warehouse': sales[4][2],
 289             'analyzed_period1_per_company': sales[0][3],
 290             'analyzed_period2_per_company': sales[1][3],
 291             'analyzed_period3_per_company': sales[2][3],
 292             'analyzed_period4_per_company': sales[3][3],
 293             'analyzed_period5_per_company': sales[4][3],
 294         })
 295         return True
 296
 297 stock_sale_forecast()
 298
 299 # The main Stock Planning object
 300 # A lot of changes by contributor in ver 1.1
 301 class stock_planning(osv.osv):
 302     _name = "stock.planning"
 303
 304     def _get_in_out(self, cr, uid, val, date_start, date_stop, direction, done, context=None):
 305         if not context:
 306             context = {}
 307         product_obj = self.pool.get('product.product')
 308         mapping = {'in': {
 309                         'field': "incoming_qty",
 310                         'adapter': lambda x: x,
 311                   },
 312                   'out': {
 313                         'field': "outgoing_qty",
 314                         'adapter': lambda x: -x,
 315                   },
 316         }
 317         context['from_date'] = date_start
 318         context['to_date'] = date_stop
 319         locations = [val.warehouse_id.lot_stock_id.id,]
 320         if not val.stock_only:
 321             locations.extend([val.warehouse_id.lot_input_id.id, val.warehouse_id.lot_output_id.id])
 322         context['location'] = locations
 323         context['compute_child'] = True
 324         prod_id = val.product_id.id
 325         if done:
 326             context.update({ 'states':('done',), 'what':(direction,) })
 327             prod_ids = [prod_id]
 328             st = product_obj.get_product_available(cr, uid, prod_ids, context=context)
 329             res = mapping[direction]['adapter'](st.get(prod_id,0.0))
 330         else:
 331             product = product_obj.read(cr, uid, prod_id,[], context)
 332             product_qty = product[mapping[direction]['field']]
 333             res = mapping[direction]['adapter'](product_qty)
 334         return res
 335
 336     def _get_outgoing_before(self, cr, uid, val, date_start, date_stop, context=None):
 337         cr.execute("SELECT sum(planning.planned_outgoing), planning.product_uom \
 338                     FROM stock_planning AS planning \
 339                     LEFT JOIN stock_period AS period \
 340                     ON (planning.period_id = period.id) \
 341                     WHERE (period.date_stop >= %s) AND (period.date_stop <= %s) \
 342                         AND (planning.product_id = %s) AND (planning.company_id = %s) \
 343                     GROUP BY planning.product_uom", \
 344                         (date_start, date_stop, val.product_id.id, val.company_id.id,))
 345         planning_qtys = cr.fetchall()
 346         res = self._to_planning_uom(cr, uid, val, planning_qtys, context)
 347         return res
 348
 349     def _to_planning_uom(self, cr, uid, val, qtys, context):
 350         res_qty = 0
 351         if qtys:
 352             uom_obj = self.pool.get('product.uom')
 353             for qty, prod_uom in qtys:
 354                 coef = self._to_default_uom_factor(cr, uid, val.product_id.id, prod_uom, context=context)
 355                 res_coef, round_value = self._from_default_uom_factor(cr, uid, val.product_id.id, val.product_uom.id, context=context)
 356                 coef = coef * res_coef
 357                 res_qty += rounding(qty * coef, round_value)
 358         return res_qty
 359
 360
 361     def _get_forecast(self, cr, uid, ids, field_names, arg, context=None):
 362         res = {}
 363         for val in self.browse(cr, uid, ids):
 364             res[val.id] = {}
 365             valid_part = val.confirmed_forecasts_only and " AND state = 'validated'" or ""
 366             cr.execute('SELECT sum(product_qty), product_uom  \
 367                         FROM stock_sale_forecast \
 368                         WHERE product_id = %s AND period_id = %s AND company_id = %s '+valid_part+ \
 369                        'GROUP BY product_uom', \
 370                             (val.product_id.id,val.period_id.id, val.company_id.id))
 371             company_qtys = cr.fetchall()
 372             res[val.id]['company_forecast'] = self._to_planning_uom(cr, uid, val, company_qtys, context)
 373
 374             cr.execute('SELECT sum(product_qty), product_uom \
 375                         FROM stock_sale_forecast \
 376                         WHERE product_id = %s and period_id = %s AND warehouse_id = %s ' + valid_part + \
 377                        'GROUP BY product_uom', \
 378                         (val.product_id.id,val.period_id.id, val.warehouse_id.id))
 379             warehouse_qtys = cr.fetchall()
 380             res[val.id]['warehouse_forecast'] = self._to_planning_uom(cr, uid, val, warehouse_qtys, context)
 381             res[val.id]['warehouse_forecast'] = rounding(res[val.id]['warehouse_forecast'],  val.product_id.uom_id.rounding)
 382         return res
 383
 384     def _get_stock_start(self, cr, uid, val, date, context=None):
 385         context['from_date'] = None
 386         context['to_date'] = date
 387         locations = [val.warehouse_id.lot_stock_id.id,]
 388         if not val.stock_only:
 389             locations.extend([val.warehouse_id.lot_input_id.id, val.warehouse_id.lot_output_id.id])
 390         context['location'] = locations
 391         context['compute_child'] = True
 392         product_obj =  self.pool.get('product.product').read(cr, uid,val.product_id.id,[], context)
 393         res = product_obj['qty_available']     # value for stock_start
 394         return res
 395
 396     def _get_past_future(self, cr, uid, ids, field_names, arg, context):
 397         res = {}
 398         for val in self.browse(cr, uid, ids, context=context):
 399             if val.period_id.date_stop < time.strftime('%Y-%m-%d'):
 400                 res[val.id] = 'Past'
 401             else:
 402                 res[val.id] = 'Future'
 403         return res
 404
 405     def _get_op(self, cr, uid, ids, field_names, arg, context=None):  # op = OrderPoint
 406         res = {}
 407         for val in self.browse(cr, uid, ids, context=context):
 408             res[val.id]={}
 409             cr.execute("SELECT product_min_qty, product_max_qty, product_uom  \
 410                         FROM stock_warehouse_orderpoint \
 411                         WHERE warehouse_id = %s AND product_id = %s AND active = 'TRUE'", (val.warehouse_id.id, val.product_id.id))
 412             ret = cr.fetchone() or [0.0,0.0,False]
 413             coef = 1
 414             round_value = 1
 415             if ret[2]:
 416                 coef = self._to_default_uom_factor(cr, uid, val.product_id.id, ret[2], context)
 417                 res_coef, round_value = self._from_default_uom_factor(cr, uid, val.product_id.id, val.product_uom.id, context=context)
 418                 coef = coef * res_coef
 419             res[val.id]['minimum_op'] = rounding(ret[0]*coef, round_value)
 420             res[val.id]['maximum_op'] = ret[1]*coef
 421         return res
 422
 423     def onchange_company(self, cr, uid, ids, company_id=False):
 424         result = {}
 425         if company_id:
 426             result['warehouse_id'] = False
 427         return {'value': result}
 428
 429     def onchange_uom(self, cr, uid, ids, product_uom=False, product_id=False, active_uom=False,
 430                      planned_outgoing=0.0, to_procure=0.0):
 431         ret = {}
 432         if not product_uom:
 433             return {}
 434         if active_uom:
 435             coeff_uom2def = self._to_default_uom_factor(cr, uid, product_id, active_uom, {})
 436             coeff_def2uom, round_value = self._from_default_uom_factor(cr, uid, product_id, product_uom, {})
 437             coeff = coeff_uom2def * coeff_def2uom
 438             ret['planned_outgoing'] = rounding(coeff * planned_outgoing, round_value)
 439             ret['to_procure'] = rounding(coeff * to_procure, round_value)
 440         ret['active_uom'] = product_uom
 441         return {'value': ret}
 442
 443     _columns = {
 444         'company_id': fields.many2one('res.company', 'Company', required = True),
 445         'history': fields.text('Procurement History', readonly=True, help = "History of procurement or internal supply of this planning line."),
 446         'state' : fields.selection([('draft','Draft'),('done','Done')],'State',readonly=True),
 447         'period_id': fields.many2one('stock.period' , 'Period', required=True, \
 448                 help = 'Period for this planning. Requisition will be created for beginning of the period.'),
 449         'warehouse_id': fields.many2one('stock.warehouse','Warehouse', required=True),
 450         'product_id': fields.many2one('product.product' , 'Product', required=True, help = 'Product which this planning is created for.'),
 451         'product_uom_categ' : fields.many2one('product.uom.categ', 'Product UoM Category'), # Invisible field for product_uom domain
 452         'product_uom': fields.many2one('product.uom', 'UoM', required=True, help = "Unit of Measure used to show the quanities of stock calculation." \
 453                         "You can use units form default category or from second category (UoS category)."),
 454         'product_uos_categ': fields.many2one('product.uom.categ', 'Product UoM Category'), # Invisible field for product_uos domain
 455 # Field used in onchange_uom to check what uom was before change to recalculate quantities acording to old uom (active_uom) and new uom.
 456         'active_uom': fields.many2one('product.uom',  string = "Active UoM"),
 457         'planned_outgoing': fields.float('Planned Out', required=True,  \
 458                 help = 'Enter planned outgoing quantity from selected Warehouse during the selected Period of selected Product. '\
 459                         'To plan this value look at Confirmed Out or Sales Forecasts. This value should be equal or greater than Confirmed Out.'),
 460         'company_forecast': fields.function(_get_forecast, method=True, string ='Company Forecast', multi = 'company', \
 461                 help = 'All sales forecasts for whole company (for all Warehouses) of selected Product during selected Period.'),
 462         'warehouse_forecast': fields.function(_get_forecast, method=True, string ='Warehouse Forecast',  multi = 'warehouse',\
 463                 help = 'All sales forecasts for selected Warehouse of selected Product during selected Period.'),
 464         'stock_simulation': fields.float('Stock Simulation', readonly =True, \
 465                 help = 'Stock simulation at the end of selected Period.\n For current period it is: \n' \
 466                        'Initial Stock - Already Out + Already In - Expected Out + Incoming Left.\n' \
 467                         'For periods ahead it is: \nInitial Stock - Planned Out Before + Incoming Before - Planned Out + Planned In.'),
 468         'incoming': fields.float('Confirmed In', readonly=True, \
 469                 help = 'Quantity of all confirmed incoming moves in calculated Period.'),
 470         'outgoing': fields.float('Confirmed Out', readonly=True, \
 471                 help = 'Quantity of all confirmed outgoing moves in calculated Period.'),
 472         'incoming_left': fields.float('Incoming Left', readonly=True,  \
 473                 help = 'Quantity left to Planned incoming quantity. This is calculated difference between Planned In and Confirmed In. ' \
 474                         'For current period Already In is also calculated. This value is used to create procurement for lacking quantity.'),
 475         'outgoing_left': fields.float('Expected Out', readonly=True, \
 476                 help = 'Quantity expected to go out in selected period. As a difference between Planned Out and Confirmed Out. ' \
 477                         'For current period Already Out is also calculated'),
 478         'to_procure': fields.float(string='Planned In', required=True, \
 479                 help = 'Enter quantity which (by your plan) should come in. Change this value and observe Stock simulation. ' \
 480                         'This value should be equal or greater than Confirmed In.'),
 481         'line_time': fields.function(_get_past_future, method=True,type='char', string='Past/Future'),
 482         'minimum_op': fields.function(_get_op, method=True, type='float', string = 'Minimum Rule', multi= 'minimum', \
 483                             help = 'Minimum quantity set in Minimum Stock Rules for this Warhouse'),
 484         'maximum_op': fields.function(_get_op, method=True, type='float', string = 'Maximum Rule', multi= 'maximum', \
 485                             help = 'Maximum quantity set in Minimum Stock Rules for this Warhouse'),
 486         'outgoing_before': fields.float('Planned Out Before', readonly=True, \
 487                             help= 'Planned Out in periods before calculated. '\
 488                                     'Between start date of current period and one day before start of calculated period.'),
 489         'incoming_before': fields.float('Incoming Before', readonly = True, \
 490                             help= 'Confirmed incoming in periods before calculated (Including Already In). '\
 491                                     'Between start date of current period and one day before start of calculated period.'),
 492         'stock_start': fields.float('Initial Stock', readonly=True, \
 493                             help= 'Stock quantity one day before current period.'),
 494         'already_out': fields.float('Already Out', readonly=True, \
 495                             help= 'Quantity which is already dispatched out of this warehouse in current period.'),
 496         'already_in': fields.float('Already In', readonly=True, \
 497                             help= 'Quantity which is already picked up to this warehouse in current period.'),
 498         'stock_only': fields.boolean("Stock Location Only", help = "Check to calculate stock location of selected warehouse only. " \
 499                                         "If not selected calculation is made for input, stock and output location of warehouse."),
 500         "procure_to_stock": fields.boolean("Procure To Stock Location", help = "Chect to make procurement to stock location of selected warehouse. " \
 501                                         "If not selected procurement will be made into input location of warehouse."),
 502         "confirmed_forecasts_only": fields.boolean("Validated Forecasts", help = "Check to take validated forecasts only. " \
 503                     "If not checked system takes validated and draft forecasts."),
 504         'supply_warehouse_id': fields.many2one('stock.warehouse','Source Warehouse', help = "Warehouse used as source in supply pick move created by 'Supply from Another Warhouse'."),
 505         "stock_supply_location": fields.boolean("Stock Supply Location", help = "Check to supply from Stock location of Supply Warehouse. " \
 506                 "If not checked supply will be made from Output location of Supply Warehouse. Used in 'Supply from Another Warhouse' with Supply Warehouse."),
 507
 508     }
 509
 510     _defaults = {
 511         'state': lambda *args: 'draft' ,
 512         'to_procure': 0.0,
 513         'planned_outgoing': 0.0,
 514         'company_id': lambda self,cr,uid,c: self.pool.get('res.company')._company_default_get(cr, uid, 'stock.planning', context=c),
 515     }
 516
 517     _order = 'period_id'
 518
 519     def _to_default_uom_factor(self, cr, uid, product_id, uom_id, context=None):
 520         uom_obj = self.pool.get('product.uom')
 521         product_obj = self.pool.get('product.product')
 522         product = product_obj.browse(cr, uid, product_id)
 523         uom = uom_obj.browse(cr, uid, uom_id, context=context)
 524         coef = uom.factor
 525         if uom.category_id.id != product.uom_id.category_id.id:
 526             coef = coef / product.uos_coeff
 527         return product.uom_id.factor / coef
 528
 529
 530     def _from_default_uom_factor(self, cr, uid, product_id, uom_id, context=None):
 531         uom_obj = self.pool.get('product.uom')
 532         product_obj = self.pool.get('product.product')
 533         product = product_obj.browse(cr, uid, product_id)
 534         uom = uom_obj.browse(cr, uid, uom_id, context=context)
 535         res = uom.factor
 536         if uom.category_id.id != product.uom_id.category_id.id:
 537             res = res / product.uos_coeff
 538         return res / product.uom_id.factor, uom.rounding
 539
 540     def calculate_planning(self, cr, uid, ids, context, *args):
 541         one_minute = relativedelta(minutes=1)
 542         current_date_beginning_c = datetime.today()
 543         current_date_end_c = current_date_beginning_c  + relativedelta(days=1, minutes=-1)  # to get hour 23:59:00
 544         current_date_beginning = current_date_beginning_c.strftime('%Y-%m-%d %H:%M:%S')
 545         current_date_end = current_date_end_c.strftime('%Y-%m-%d %H:%M:%S')
 546         for val in self.browse(cr, uid, ids, context=context):
 547             day = datetime.strptime(val.period_id.date_start, '%Y-%m-%d %H:%M:%S')
 548             dbefore = datetime(day.year, day.month, day.day) - one_minute
 549             day_before_calculated_period = dbefore.strftime('%Y-%m-%d %H:%M:%S')   # one day before start of calculated period
 550             cr.execute("SELECT date_start \
 551                     FROM stock_period AS period \
 552                     LEFT JOIN stock_planning AS planning \
 553                     ON (planning.period_id = period.id) \
 554                     WHERE (period.date_stop >= %s) AND (period.date_start <= %s) AND \
 555                         planning.product_id = %s", (current_date_end, current_date_end, val.product_id.id,)) #
 556             date = cr.fetchone()
 557             start_date_current_period = date and date[0] or False
 558             start_date_current_period = start_date_current_period or current_date_beginning
 559
 560             day = datetime.strptime(start_date_current_period, '%Y-%m-%d %H:%M:%S')
 561             dbefore = datetime(day.year, day.month, day.day) - one_minute
 562             date_for_start = dbefore.strftime('%Y-%m-%d %H:%M:%S')   # one day before current period
 563             already_out = self._get_in_out(cr, uid, val, start_date_current_period, current_date_end, direction='out', done=True, context=context),
 564             already_in = self._get_in_out(cr, uid, val, start_date_current_period, current_date_end, direction='in', done=True, context=context),
 565             outgoing = self._get_in_out(cr, uid, val, val.period_id.date_start, val.period_id.date_stop, direction='out', done=False, context=context),
 566             incoming = self._get_in_out(cr, uid, val, val.period_id.date_start, val.period_id.date_stop, direction='in', done=False, context=context),
 567             outgoing_before = self._get_outgoing_before(cr, uid, val, start_date_current_period, day_before_calculated_period, context=context),
 568             incoming_before = self._get_in_out(cr, uid, val, start_date_current_period, day_before_calculated_period, direction='in', done=False, context=context),
 569             stock_start = self._get_stock_start(cr, uid, val, date_for_start, context=context),
 570             if start_date_current_period == val.period_id.date_start:   # current period is calculated
 571                 current = True
 572             else:
 573                 current = False
 574             factor, round_value = self._from_default_uom_factor(cr, uid, val.product_id.id, val.product_uom.id, context=context)
 575             self.write(cr, uid, ids, {
 576                 'already_out': rounding(already_out[0]*factor,round_value),
 577                 'already_in': rounding(already_in[0]*factor,round_value),
 578                 'outgoing': rounding(outgoing[0]*factor,round_value),
 579                 'incoming': rounding(incoming[0]*factor,round_value),
 580                 'outgoing_before' : rounding(outgoing_before[0]*factor,round_value),
 581                 'incoming_before': rounding((incoming_before[0]+ (not current and already_in[0]))*factor,round_value),
 582                 'outgoing_left': rounding(val.planned_outgoing - (outgoing[0] + (current and already_out[0]))*factor,round_value),
 583                 'incoming_left': rounding(val.to_procure - (incoming[0] + (current and already_in[0]))*factor,round_value),
 584                 'stock_start': rounding(stock_start[0]*factor,round_value),
 585                 'stock_simulation': rounding(val.to_procure - val.planned_outgoing + (stock_start[0]+ incoming_before[0] - outgoing_before[0] \
 586                                      + (not current and already_in[0]))*factor,round_value),
 587             })
 588         return True
 589
 590 # method below converts quantities and uoms to general OpenERP standard with UoM Qty, UoM, UoS Qty, UoS.
 591 # from stock_planning standard where you have one Qty and one UoM (any from UoM or UoS category)
 592 # so if UoM is from UoM category it is used as UoM in standard and if product has UoS the UoS will be calcualated.
 593 # If UoM is from UoS category it is recalculated to basic UoS from product (in planning you can use any UoS from UoS category)
 594 # and basic UoM is calculated.
 595     def _qty_to_standard(self, cr, uid, val, context):
 596         uos = False
 597         uos_qty = 0.0
 598         if val.product_uom.category_id.id == val.product_id.uom_id.category_id.id:
 599             uom_qty = val.incoming_left
 600             uom = val.product_uom.id
 601             if val.product_id.uos_id:
 602                 uos = val.product_id.uos_id.id
 603                 coeff_uom2def = self._to_default_uom_factor(cr, uid, val.product_id.id, val.product_uom.id, {})
 604                 coeff_def2uom, round_value = self._from_default_uom_factor(cr, uid, val.product_id.id, uos, {})
 605                 uos_qty = rounding(val.incoming_left * coeff_uom2def * coeff_def2uom, round_value)
 606         elif val.product_uom.category_id.id == val.product_id.uos_id.category_id.id:
 607             coeff_uom2def = self._to_default_uom_factor(cr, uid, val.product_id.id, val.product_uom.id, {})
 608             uos = val.product_id.uos_id.id
 609             coeff_def2uom, round_value = self._from_default_uom_factor(cr, uid, val.product_id.id, uos, {})
 610             uos_qty = rounding(val.incoming_left * coeff_uom2def * coeff_def2uom, round_value)
 611             uom = val.product_id.uom_id.id
 612             coeff_def2uom, round_value = self._from_default_uom_factor(cr, uid, val.product_id.id, uom, {})
 613             uom_qty = rounding(val.incoming_left * coeff_uom2def * coeff_def2uom, round_value)
 614         return uom_qty, uom, uos_qty, uos
 615
 616     def procure_incomming_left(self, cr, uid, ids, context, *args):
 617         for obj in self.browse(cr, uid, ids):
 618             if obj.incoming_left <= 0:
 619                 raise osv.except_osv(_('Error !'), _('Incoming Left must be greater than 0 !'))
 620             uom_qty, uom, uos_qty, uos = self._qty_to_standard(cr, uid, obj, context)
 621             user = self.pool.get('res.users').browse(cr, uid, uid, context)
 622             proc_id = self.pool.get('procurement.order').create(cr, uid, {
 623                         'company_id' : obj.company_id.id,
 624                         'name': _('Manual planning for ') + obj.period_id.name,
 625                         'origin': _('MPS(') + str(user.login) +') '+ obj.period_id.name,
 626                         'date_planned': obj.period_id.date_start,
 627                         'product_id': obj.product_id.id,
 628                         'product_qty': uom_qty,
 629                         'product_uom': uom,
 630                         'product_uos_qty': uos_qty,
 631                         'product_uos': uos,
 632                         'location_id': obj.procure_to_stock and obj.warehouse_id.lot_stock_id.id or obj.warehouse_id.lot_input_id.id,
 633                         'procure_method': 'make_to_order',
 634                         'note' : _("Procurement created in MPS by user: ") + str(user.login) + _("  Creation Date: ") + \
 635                                             time.strftime('%Y-%m-%d %H:%M:%S') + \
 636                                         _("\nFor period: ") + obj.period_id.name + _(" according to state:") + \
 637                                         _("\n Warehouse Forecast: ") + str(obj.warehouse_forecast) + \
 638                                         _("\n Initial Stock: ") + str(obj.stock_start) + \
 639                                         _("\n Planned Out: ") + str(obj.planned_outgoing) + _("    Planned In: ") + str(obj.to_procure) + \
 640                                         _("\n Already Out: ") + str(obj.already_out) + _("    Already In: ") +  str(obj.already_in) + \
 641                                         _("\n Confirmed Out: ") + str(obj.outgoing) + _("    Confirmed In: ") + str(obj.incoming) + \
 642                                         _("\n Planned Out Before: ") + str(obj.outgoing_before) + _("    Confirmed In Before: ") + \
 643                                                                                             str(obj.incoming_before) + \
 644                                         _("\n Expected Out: ") + str(obj.outgoing_left) + _("    Incoming Left: ") + str(obj.incoming_left) + \
 645                                         _("\n Stock Simulation: ") +  str(obj.stock_simulation) + _("    Minimum stock: ") + str(obj.minimum_op),
 646
 647                             }, context=context)
 648             wf_service = netsvc.LocalService("workflow")
 649             wf_service.trg_validate(uid, 'procurement.order', proc_id, 'button_confirm', cr)
 650             self.calculate_planning(cr, uid, ids, context)
 651             prev_text = obj.history or ""
 652             self.write(cr, uid, ids, {
 653                     'history': prev_text + _('Requisition (') + str(user.login) + ", " + time.strftime('%Y.%m.%d %H:%M) ') + str(obj.incoming_left) + \
 654                     " " + obj.product_uom.name + "\n",
 655                 })
 656         return True
 657
 658     def internal_supply(self, cr, uid, ids, context, *args):
 659         for obj in self.browse(cr, uid, ids):
 660             if obj.incoming_left <= 0:
 661                 raise osv.except_osv(_('Error !'), _('Incoming Left must be greater than 0 !'))
 662             if not obj.supply_warehouse_id:
 663                 raise osv.except_osv(_('Error !'), _('You must specify a Source Warehouse !'))
 664             if obj.supply_warehouse_id.id == obj.warehouse_id.id:
 665                 raise osv.except_osv(_('Error !'), _('You must specify a Source Warehouse different than calculated (destination) Warehouse !'))
 666             uom_qty, uom, uos_qty, uos = self._qty_to_standard(cr, uid, obj, context)
 667             user = self.pool.get('res.users').browse(cr, uid, uid, context)
 668             picking_id = self.pool.get('stock.picking').create(cr, uid, {
 669                             'origin': _('MPS(') + str(user.login) +') '+ obj.period_id.name,
 670                             'type': 'internal',
 671                             'state': 'auto',
 672                             'date': obj.period_id.date_start,
 673                             'move_type': 'direct',
 674                             'invoice_state':  'none',
 675                             'company_id': obj.company_id.id,
 676                             'note': _("Pick created from MPS by user: ") + str(user.login) + _("  Creation Date: ") + \
 677                                             time.strftime('%Y-%m-%d %H:%M:%S') + \
 678                                         _("\nFor period: ") + obj.period_id.name + _(" according to state:") + \
 679                                         _("\n Warehouse Forecast: ") + str(obj.warehouse_forecast) + \
 680                                         _("\n Initial Stock: ") + str(obj.stock_start) + \
 681                                         _("\n Planned Out: ") + str(obj.planned_outgoing) + _("    Planned In: ") + str(obj.to_procure) + \
 682                                         _("\n Already Out: ") + str(obj.already_out) + _("    Already In: ") +  str(obj.already_in) + \
 683                                         _("\n Confirmed Out: ") + str(obj.outgoing) + _("    Confirmed In: ") + str(obj.incoming) + \
 684                                         _("\n Planned Out Before: ") + str(obj.outgoing_before) + _("    Confirmed In Before: ") + \
 685                                                                                             str(obj.incoming_before) + \
 686                                         _("\n Expected Out: ") + str(obj.outgoing_left) + _("    Incoming Left: ") + str(obj.incoming_left) + \
 687                                         _("\n Stock Simulation: ") +  str(obj.stock_simulation) + _("    Minimum stock: ") + str(obj.minimum_op),
 688                         })
 689
 690             move_id = self.pool.get('stock.move').create(cr, uid, {
 691                         'name': _('MPS(') + str(user.login) +') '+ obj.period_id.name,
 692                         'picking_id': picking_id,
 693                         'product_id': obj.product_id.id,
 694                         'date': obj.period_id.date_start,
 695                         'product_qty': uom_qty,
 696                         'product_uom': uom,
 697                         'product_uos_qty': uos_qty,
 698                         'product_uos': uos,
 699                         'location_id': obj.stock_supply_location and obj.supply_warehouse_id.lot_stock_id.id or \
 700                                                                 obj.supply_warehouse_id.lot_output_id.id,
 701                         'location_dest_id': obj.procure_to_stock and obj.warehouse_id.lot_stock_id.id or \
 702                                                                 obj.warehouse_id.lot_input_id.id,
 703                         'tracking_id': False,
 704                         'company_id': obj.company_id.id,
 705                     })
 706             wf_service = netsvc.LocalService("workflow")
 707             wf_service.trg_validate(uid, 'stock.picking', picking_id, 'button_confirm', cr)
 708
 709         self.calculate_planning(cr, uid, ids, context)
 710         prev_text = obj.history or ""
 711         pick_name = self.pool.get('stock.picking').browse(cr, uid, picking_id).name
 712         self.write(cr, uid, ids, {
 713               'history' : prev_text + _('Pick List ')+ pick_name + " (" + str(user.login) + ", " + time.strftime('%Y.%m.%d %H:%M) ') \
 714                 + str(obj.incoming_left) +" " + obj.product_uom.name + "\n",
 715                 })
 716
 717         return True
 718
 719     def product_id_change(self, cr, uid, ids, product_id):
 720         ret = {}
 721         if product_id:
 722             product_rec =  self.pool.get('product.product').browse(cr, uid, product_id)
 723             ret['product_uom'] = product_rec.uom_id.id
 724             ret['active_uom'] = product_rec.uom_id.id
 725             ret['product_uom_categ'] = product_rec.uom_id.category_id.id
 726             ret['product_uos_categ'] = product_rec.uos_id and product_rec.uos_id.category_id.id or False
 727         else:
 728             ret['product_uom'] = False
 729             ret['product_uom_categ'] = False
 730             ret['product_uos_categ'] = False
 731         res = {'value': ret}
 732         return res
 733
 734 stock_planning()
 735
 736 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: