1 # -*- coding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
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.
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.
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/>.
20 ##############################################################################
23 from osv import fields,osv
24 from decimal_precision import decimal_precision as dp
27 class report_stock_move(osv.osv):
28 _name = "report.stock.move"
29 _description = "Moves Statistics"
32 'date': fields.date('Date', readonly=True),
33 'year': fields.char('Year', size=4, readonly=True),
34 'day': fields.char('Day', size=128, readonly=True),
35 'month':fields.selection([('01','January'), ('02','February'), ('03','March'), ('04','April'),
36 ('05','May'), ('06','June'), ('07','July'), ('08','August'), ('09','September'),
37 ('10','October'), ('11','November'), ('12','December')], 'Month',readonly=True),
38 'partner_id':fields.many2one('res.partner', 'Partner', readonly=True),
39 'product_id':fields.many2one('product.product', 'Product', readonly=True),
40 'company_id':fields.many2one('res.company', 'Company', readonly=True),
41 'picking_id':fields.many2one('stock.picking', 'Shipment', readonly=True),
42 'type': fields.selection([('out', 'Sending Goods'), ('in', 'Getting Goods'), ('internal', 'Internal'), ('other', 'Others')], 'Shipping Type', required=True, select=True, help="Shipping type specify, goods coming in or going out."),
43 'location_id': fields.many2one('stock.location', 'Source Location', readonly=True, select=True, help="Sets a location if you produce at a fixed location. This can be a partner location if you subcontract the manufacturing operations."),
44 'location_dest_id': fields.many2one('stock.location', 'Dest. Location', readonly=True, select=True, help="Location where the system will stock the finished products."),
45 'state': fields.selection([('draft', 'Draft'), ('waiting', 'Waiting'), ('confirmed', 'Confirmed'), ('assigned', 'Available'), ('done', 'Done'), ('cancel', 'Cancelled')], 'Status', readonly=True, select=True),
46 'product_qty':fields.integer('Quantity',readonly=True),
47 'categ_id': fields.many2one('product.category', 'Product Category', ),
48 'product_qty_in':fields.integer('In Qty',readonly=True),
49 'product_qty_out':fields.integer('Out Qty',readonly=True),
50 'value' : fields.float('Total Value', required=True),
51 'day_diff2':fields.float('Lag (Days)',readonly=True, digits_compute=dp.get_precision('Shipping Delay'), group_operator="avg"),
52 'day_diff1':fields.float('Planned Lead Time (Days)',readonly=True, digits_compute=dp.get_precision('Shipping Delay'), group_operator="avg"),
53 'day_diff':fields.float('Execution Lead Time (Days)',readonly=True, digits_compute=dp.get_precision('Shipping Delay'), group_operator="avg"),
54 'stock_journal': fields.many2one('stock.journal','Stock Journal', select=True),
60 tools.drop_view_if_exists(cr, 'report_stock_move')
62 CREATE OR REPLACE view report_stock_move AS (
65 date_trunc('day',al.dp) as date,
67 al.curr_month as month,
69 al.curr_day_diff as day_diff,
70 al.curr_day_diff1 as day_diff1,
71 al.curr_day_diff2 as day_diff2,
72 al.location_id as location_id,
73 al.picking_id as picking_id,
74 al.company_id as company_id,
75 al.location_dest_id as location_dest_id,
77 al.out_qty as product_qty_out,
78 al.in_qty as product_qty_in,
79 al.partner_id as partner_id,
80 al.product_id as product_id,
82 al.product_uom as product_uom,
83 al.categ_id as categ_id,
84 coalesce(al.type, 'other') as type,
85 al.stock_journal as stock_journal,
86 sum(al.in_value - al.out_value) as value
88 CASE WHEN sp.type in ('out') THEN
89 sum(sm.product_qty * pu.factor / pu2.factor)
92 CASE WHEN sp.type in ('in') THEN
93 sum(sm.product_qty * pu.factor / pu2.factor)
96 CASE WHEN sp.type in ('out') THEN
97 sum(sm.product_qty * pu.factor / pu2.factor) * pt.standard_price
100 CASE WHEN sp.type in ('in') THEN
101 sum(sm.product_qty * pu.factor / pu2.factor) * pt.standard_price
106 to_char(date_trunc('day',sm.date), 'YYYY') as curr_year,
107 to_char(date_trunc('day',sm.date), 'MM') as curr_month,
108 to_char(date_trunc('day',sm.date), 'YYYY-MM-DD') as curr_day,
109 avg(date(sm.date)-date(sm.create_date)) as curr_day_diff,
110 avg(date(sm.date_expected)-date(sm.create_date)) as curr_day_diff1,
111 avg(date(sm.date)-date(sm.date_expected)) as curr_day_diff2,
112 sm.location_id as location_id,
113 sm.location_dest_id as location_dest_id,
114 sum(sm.product_qty) as product_qty,
115 pt.categ_id as categ_id ,
116 sm.partner_id as partner_id,
117 sm.product_id as product_id,
118 sm.picking_id as picking_id,
119 sm.company_id as company_id,
121 sm.product_uom as product_uom,
123 sp.stock_journal_id AS stock_journal
126 LEFT JOIN stock_picking sp ON (sm.picking_id=sp.id)
127 LEFT JOIN product_product pp ON (sm.product_id=pp.id)
128 LEFT JOIN product_uom pu ON (sm.product_uom=pu.id)
129 LEFT JOIN product_uom pu2 ON (sm.product_uom=pu2.id)
130 LEFT JOIN product_template pt ON (pp.product_tmpl_id=pt.id)
132 sm.id,sp.type, sm.date,sm.partner_id,
133 sm.product_id,sm.state,sm.product_uom,sm.date_expected,
134 sm.product_id,pt.standard_price, sm.picking_id, sm.product_qty,
135 sm.company_id,sm.product_qty, sm.location_id,sm.location_dest_id,pu.factor,pt.categ_id, sp.stock_journal_id)
138 al.out_qty,al.in_qty,al.curr_year,al.curr_month,
139 al.curr_day,al.curr_day_diff,al.curr_day_diff1,al.curr_day_diff2,al.dp,al.location_id,al.location_dest_id,
140 al.partner_id,al.product_id,al.state,al.product_uom,
141 al.picking_id,al.company_id,al.type,al.product_qty, al.categ_id, al.stock_journal
148 class report_stock_inventory(osv.osv):
149 _name = "report.stock.inventory"
150 _description = "Stock Statistics"
153 'date': fields.datetime('Date', readonly=True),
154 'year': fields.char('Year', size=4, readonly=True),
155 'month':fields.selection([('01','January'), ('02','February'), ('03','March'), ('04','April'),
156 ('05','May'), ('06','June'), ('07','July'), ('08','August'), ('09','September'),
157 ('10','October'), ('11','November'), ('12','December')], 'Month', readonly=True),
158 'partner_id':fields.many2one('res.partner', 'Partner', readonly=True),
159 'product_id':fields.many2one('product.product', 'Product', readonly=True),
160 'product_categ_id':fields.many2one('product.category', 'Product Category', readonly=True),
161 'location_id': fields.many2one('stock.location', 'Location', readonly=True),
162 'prodlot_id': fields.many2one('stock.production.lot', 'Lot', readonly=True),
163 'company_id': fields.many2one('res.company', 'Company', readonly=True),
164 'product_qty':fields.float('Quantity', digits_compute=dp.get_precision('Product Unit of Measure'), readonly=True),
165 'value' : fields.float('Total Value', digits_compute=dp.get_precision('Account'), required=True),
166 'state': fields.selection([('draft', 'Draft'), ('waiting', 'Waiting'), ('confirmed', 'Confirmed'), ('assigned', 'Available'), ('done', 'Done'), ('cancel', 'Cancelled')], 'Status', readonly=True, select=True,
167 help='When the stock move is created it is in the \'Draft\' state.\n After that it is set to \'Confirmed\' state.\n If stock is available state is set to \'Avaiable\'.\n When the picking it done the state is \'Done\'.\
168 \nThe state is \'Waiting\' if the move is waiting for another one.'),
169 'location_type': fields.selection([('supplier', 'Supplier Location'), ('view', 'View'), ('internal', 'Internal Location'), ('customer', 'Customer Location'), ('inventory', 'Inventory'), ('procurement', 'Procurement'), ('production', 'Production'), ('transit', 'Transit Location for Inter-Companies Transfers')], 'Location Type', required=True),
170 'scrap_location': fields.boolean('scrap'),
173 tools.drop_view_if_exists(cr, 'report_stock_inventory')
175 CREATE OR REPLACE view report_stock_inventory AS (
177 min(m.id) as id, m.date as date,
178 to_char(m.date, 'YYYY') as year,
179 to_char(m.date, 'MM') as month,
180 m.partner_id as partner_id, m.location_id as location_id,
181 m.product_id as product_id, pt.categ_id as product_categ_id, l.usage as location_type, l.scrap_location as scrap_location,
183 m.state as state, m.prodlot_id as prodlot_id,
185 coalesce(sum(-pt.standard_price * m.product_qty * pu.factor / pu2.factor)::decimal, 0.0) as value,
186 coalesce(sum(-m.product_qty * pu.factor / pu2.factor)::decimal, 0.0) as product_qty
189 LEFT JOIN stock_picking p ON (m.picking_id=p.id)
190 LEFT JOIN product_product pp ON (m.product_id=pp.id)
191 LEFT JOIN product_template pt ON (pp.product_tmpl_id=pt.id)
192 LEFT JOIN product_uom pu ON (pt.uom_id=pu.id)
193 LEFT JOIN product_uom pu2 ON (m.product_uom=pu2.id)
194 LEFT JOIN product_uom u ON (m.product_uom=u.id)
195 LEFT JOIN stock_location l ON (m.location_id=l.id)
197 m.id, m.product_id, m.product_uom, pt.categ_id, m.partner_id, m.location_id, m.location_dest_id,
198 m.prodlot_id, m.date, m.state, l.usage, l.scrap_location, m.company_id, pt.uom_id, to_char(m.date, 'YYYY'), to_char(m.date, 'MM')
201 -m.id as id, m.date as date,
202 to_char(m.date, 'YYYY') as year,
203 to_char(m.date, 'MM') as month,
204 m.partner_id as partner_id, m.location_dest_id as location_id,
205 m.product_id as product_id, pt.categ_id as product_categ_id, l.usage as location_type, l.scrap_location as scrap_location,
207 m.state as state, m.prodlot_id as prodlot_id,
208 coalesce(sum(pt.standard_price * m.product_qty * pu.factor / pu2.factor)::decimal, 0.0) as value,
209 coalesce(sum(m.product_qty * pu.factor / pu2.factor)::decimal, 0.0) as product_qty
212 LEFT JOIN stock_picking p ON (m.picking_id=p.id)
213 LEFT JOIN product_product pp ON (m.product_id=pp.id)
214 LEFT JOIN product_template pt ON (pp.product_tmpl_id=pt.id)
215 LEFT JOIN product_uom pu ON (pt.uom_id=pu.id)
216 LEFT JOIN product_uom pu2 ON (m.product_uom=pu2.id)
217 LEFT JOIN product_uom u ON (m.product_uom=u.id)
218 LEFT JOIN stock_location l ON (m.location_dest_id=l.id)
220 m.id, m.product_id, m.product_uom, pt.categ_id, m.partner_id, m.location_id, m.location_dest_id,
221 m.prodlot_id, m.date, m.state, l.usage, l.scrap_location, m.company_id, pt.uom_id, to_char(m.date, 'YYYY'), to_char(m.date, 'MM')
225 report_stock_inventory()
229 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: