Hello everyone,
I would like to do something like this: location = Production
1. within the selected range of time 'start_date' and 'end_date'
2. count product in location for every week, then average the weekly quantity,
3. count total of the product in location for a each month within this range of time and average the monthly quantity
4. based on this information calculate minimum of the product in stock 'MWoS',
5. check if product level is lower than '150% of MWoS' if so send message or give a notification about the product ;)
I know, I can do something like this directly in Odoo but the whole process is far too complicated so I decided to create an addon which will do this thing in one click .... ok, not in one ... in four :)
But still it's quicker than digging, setting everything up for each product etc.
So far I can check quantities in location, count it within selected range of time then check details if needed.
Code:
stock_check(osv.osv):_name = 'stock.check'
def _days_between(self, cr, uid, ids, field_name, arg, context=None):
res = {}
for cur_data in self.browse(cr, uid, ids, context=context):
st_date = datetime.strptime(cur_data.st_date, "%Y-%m-%d")
en_date = datetime.strptime(cur_data.en_date, "%Y-%m-%d")
res[cur_data.id] = abs((en_date - st_date).days)
return res
def _weeks_between(self, cr, uid, ids, field_name, arg, context=None):
res = {}
for cur_data in self.browse(cr, uid, ids, context=context):
st_date = datetime.strptime(cur_data.st_date, "%Y-%m-%d")
en_date = datetime.strptime(cur_data.en_date, "%Y-%m-%d")
week_day1 = (st_date - timedelta(days=st_date.weekday()))
week_day2 = (en_date - timedelta(days=en_date.weekday()))
res[cur_data.id] = (week_day2 - week_day1).days / 7
return res
_columns = {
'product': fields.one2many('product.data','check_id', string="Product"),
'location_id': fields.many2one('stock.location','location'),
'state': fields.boolean('checked'),
'st_date': fields.date('Start date'),
'en_date': fields.date('End date'),
'days': fields.function(_days_between, type='char', string='NoD'),
'weeks': fields.function(_weeks_between, type='char', string='NoW'),
}
def _get_def_loc(self, cr, uid, context=None):
dest_loc = self.pool.get('stock.location').search(cr, uid, [('usage', '=', 'production')], context=context)
dest_loc_obj = self.pool.get('stock.location').browse(cr, uid, dest_loc[0])
return dest_loc_obj.id
_defaults = {
'location_id': _get_def_loc,
}
def moves_get(self, cr, uid, ids, context=None):
a = 0
res=[]
total = 0
lsrv = 0
hsrv = 0
asrv = 0
mwosres = 0
tot_qty = 0
product_id = 0
product_qty = 0
if context.has_key('prod_id'):
product_id = context.get('prod_id', 0)
for obj in self.browse(cr, uid, ids):
cr.execute(""" select product_id,sum(qty) from stock_quant where in_date ::date>=to_date('%s', 'YYYY-MM-DD') and
in_date ::date<= to_date('%s', 'YYYY-MM-DD') and location_id =%s group by product_id;""" %(obj.st_date,obj.en_date, obj.location_id.id))
tot_qty = cr.fetchall()
if product_id == True:
print 'Product ', product_id, ' removed'
else:
if tot_qty:
for rec in tot_qty:
a = a + 1
total += rec[1]
lsrv = min(str,rec[1])
hsrv = rec[1]
asrv = (total / a)
mwosres = (rec[1]/7 * 52)
res.append((0, 0, {'product_id': rec[0],'product_qty':rec[1],'lsr':lsrv,'hsr':hsrv,'asr':asrv,'mwos':mwosres}))
ab = obj.write({'product':res})
print '========================================================================'
print 'RESULT:', res
print 'MWoS: ', mwosres
print 'LSR: ', lsrv
print 'HSR: ', hsrv
print 'ASR: ', asrv
print 'TOTAL: ', total
print 'TorF: ', ab
print '========================================================================'
# if mwosres > 55:
# raise except_orm('FOO','Low MWoS')
# else:
# raise Warning('Stock is OK!')
class product_data(osv.osv):
_name = 'product.data'
_columns = {
'product_id': fields.many2one('product.template', string='Product'),
'product_qty': fields.integer('Quantity'),
'check_id': fields.many2one('stock.check', 'Check Master'),
'mwos': fields.integer('MWoS', help='Month Worth of Stock value'),
'hsr': fields.char('HMS - highest sell rate'),
'lsr': fields.char('LSR - lowest sell rate'),
'asr': fields.char('ASR - average sell rate'),
'image': fields.binary(related='product_id.image_medium', string='Product Image'),
'analytic_data': fields.one2many('product.analyze','analyze_id', string='Analytic data'),
'location_id': fields.many2one('stock.location','location'),
}
def _get_def_loc(self, cr, uid, context=None):
dest_loc = self.pool.get('stock.location').search(cr, uid, [('usage', '=', 'production')], context=context)
dest_loc_obj = self.pool.get('stock.location').browse(cr, uid, dest_loc[0])
return dest_loc_obj.id
_defaults = {
'location_id': _get_def_loc,
}
def quants_get(self, cr, uid, ids, context=None):
res=[]
tot_qty = 0
product_id = 0
product_qty = 0
if context.has_key('prod_id'):
product_id = context.get('prod_id', 0)
for obj in self.browse(cr, uid, ids):
cr.execute(""" select in_date, sum(qty) from stock_quant where product_id = %s and location_id =%s group by in_date;""" %(obj.product_id.id, obj.location_id.id))
tot_qty = cr.fetchall()
if tot_qty:
for rec in tot_qty:
res.append((0, 0, {'analyze_id':rec[0],'sell_date':rec[0],'weekly_sell':rec[1],'monthly_sell':rec[1]}))
ab = obj.write({'analytic_data':res})
print '========================================================================'
print res
print '========================================================================'
class product_analyze(osv.osv):
_name = 'product.analyze'
_columns = {
'analyze_id': fields.many2one('product.data','Analyze'),
'sell_date': fields.char('Sell date'),
'monthly_sell': fields.integer('Monthly sell'),
'weekly_sell': fields.integer('Weekly sell'),
}
In a code there is a definition which count days, weeks (don't need days :)) need months, but how to use it to check and count product for every week and record it, then check for a month and record it ?
I hope you know what I mean .......