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