[MOD] Improvement in Sql view

bzr revid: vir@tinyerp.com-20100202131024-ohz2u0s3hfxe2ko2

3 files changed, 44 insertions, 15 deletions

diff --git a/addons/board_warehouse/board_warehouse_view.xml b/addons/board_warehouse/board_warehouse_view.xml
index 0ea7fb48297..cdc93d9a274 100644
--- a/addons/board_warehouse/board_warehouse_view.xml
+++ b/addons/board_warehouse/board_warehouse_view.xml
@@ -5,6 +5,15 @@
             <field name="name">Warehouse</field>
         </record>
 
+		<record id="action_reception_picking_move_board" model="ir.actions.act_window">
+            <field name="name">Reception Picking Stock Move</field>
+            <field name="res_model">stock.move</field>
+            <field name="type">ir.actions.act_window</field>
+            <field name="view_type">form</field>
+            <field name="view_mode">tree,form</field>
+            <field name="domain">[('picking_id','!=',False),('picking_id.type','=','in'),('date','&lt;=',time.strftime('%Y-%m-%d 23:59:59'))]</field>
+            <field name="view_id" ref="stock.view_move_tree_reception_picking"/>
+        </record>
 
 		<record id="board_warehouse_form" model="ir.ui.view">
             <field name="name">board.warehouse.form</field>
@@ -15,11 +24,11 @@
                     <hpaned position="100">
                         <child1>
 					    	<action colspan="4" name="%(mrp.mrp_procurement_action5)d" string="Procurement Exceptions" width="510" />
-					    	<action colspan="4" name="%(stock.action_reception_picking_move)d" string="Products To Receive" />
+					    	<action colspan="4" name="%(action_reception_picking_move_board)d" string="Products To Receive" />
                         </child1>
 
                         <child2>
-					    	<action colspan="4" name="%(stock.action_move_graph_reception_picking)d" string="Products To Receive" width="510"/>
+					    	<action colspan="4" name="%(stock.action_move_graph_reception_picking)d" string="Number of Products to receive Vs planned" width="510"/>
 					    	<action colspan="4" name="%(stock.action_move_delivery_products_planned)d" string="Delivery Products Vs Planned" width="510"/>
                         </child2>
                     </hpaned>
diff --git a/addons/stock/stock.py b/addons/stock/stock.py
index 1e91f182568..cc4da8bdbbd 100644
--- a/addons/stock/stock.py
+++ b/addons/stock/stock.py
@@ -1656,12 +1656,21 @@ class report_products_to_received_planned(osv.osv):
         tools.drop_view_if_exists(cr, 'report_products_to_received_planned')
         cr.execute("""
             create or replace view report_products_to_received_planned as (
-               select
-                     distinct current_date as date,
-                     min(id) as id,
-                     (select sum(product_qty)  from  stock_move where to_char(date,'YYYY-MM-DD')::date = current_date ) as qty,
-                     (select sum(product_qty)  from  stock_move where to_char(date_planned,'YYYY-MM-DD')::date = current_date) as planned_qty
-                from  stock_move
+               select stock.date, min(stock.id), sum(stock.product_qty) as qty, 0 as planned_qty
+                   from stock_picking picking
+                    inner join stock_move stock
+                    on picking.id = stock.picking_id and picking.type = 'in'
+                    where stock.date between (select cast(date_trunc('week', current_date) as date)) and (select cast(date_trunc('week', current_date) as date) + 7)
+                    group by stock.date
+
+                    union
+
+               select stock.date_planned, min(stock.id), 0 as actual_qty, sum(stock.product_qty) as planned_qty
+                    from stock_picking picking
+                    inner join stock_move stock
+                    on picking.id = stock.picking_id and picking.type = 'in'
+                    where stock.date_planned between (select cast(date_trunc('week', current_date) as date)) and (select cast(date_trunc('week', current_date) as date) + 7)
+        group by stock.date_planned
                 )
         """)
 report_products_to_received_planned()
@@ -1681,12 +1690,23 @@ class report_delivery_products_planned(osv.osv):
         tools.drop_view_if_exists(cr, 'report_delivery_products_planned')
         cr.execute("""
             create or replace view report_delivery_products_planned as (
-                select
-                     distinct current_date as date,
-                     min(sm.id) as id,
-                     (select sum(product_qty)  from  stock_move join stock_picking sp on (sm.picking_id = sp.id ) where to_char(sm.date,'YYYY-MM-DD')::date = current_date ) as qty,
-                     (select sum(product_qty)  from  stock_move join stock_picking sp on (sm.picking_id = sp.id ) where to_char(sm.date_planned,'YYYY-MM-DD')::date = current_date) as planned_qty
-                from  stock_move sm join stock_picking sp on (sm.picking_id = sp.id ) where sp.type = 'out' group by sm.picking_id, sm.date, sm.date_planned
+                select stock.date, min(stock.id), sum(stock.product_qty) as qty, 0 as planned_qty
+                   from stock_picking picking
+                    inner join stock_move stock
+                    on picking.id = stock.picking_id and picking.type = 'out'
+                    where stock.date between (select cast(date_trunc('week', current_date) as date)) and (select cast(date_trunc('week', current_date) as date) + 7)
+                    group by stock.date
+
+                    union
+
+               select stock.date_planned, min(stock.id), 0 as actual_qty, sum(stock.product_qty) as planned_qty
+                    from stock_picking picking
+                    inner join stock_move stock
+                    on picking.id = stock.picking_id and picking.type = 'out'
+                    where stock.date_planned between (select cast(date_trunc('week', current_date) as date)) and (select cast(date_trunc('week', current_date) as date) + 7)
+        group by stock.date_planned
+
+
                 )
         """)
 report_delivery_products_planned()
diff --git a/addons/stock/stock_view.xml b/addons/stock/stock_view.xml
index 9adf0bbecba..42bef7b13d7 100644
--- a/addons/stock/stock_view.xml
+++ b/addons/stock/stock_view.xml
@@ -1515,7 +1515,7 @@
 	    </record>
 
 	    <record id="action_move_graph_reception_picking" model="ir.actions.act_window">
-	        <field name="name">Reception Picking Stock Move</field>
+	        <field name="name">Number of Products to receive Vs planned</field>
 	        <field name="res_model">report.products.to.received.planned</field>
 	        <field name="type">ir.actions.act_window</field>
 	        <field name="view_type">form</field>