my_report/my_report.py

#                                                                               #
#    You should have received a copy of the GNU Affero General Public License   #
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.      #
#                                                                               #
#################################################################################

from openerp.osv import fields,osv
from openerp import tools

class my_report_model(osv.osv):
	_name = "my.report.model"
	_description = "Listado de productos mas vendidos"
	_auto = False
	_columns = {
		'product_id': fields.many2one('product.product', string='Producto', required=True, readonly=True),
		'nombre': fields.char('Nombre', size=128, readonly=True),
		'suma': fields.float('Cantidad', readonly=True),
		'lastdate': fields.date('Fecha Ult.Venta', readonly=True)
	}
	_order = 'suma ASC'

	def init(self, cr):
		tools.sql.drop_view_if_exists(cr, 'my_report_model')
		cr.execute("""
            CREATE OR REPLACE VIEW my_report_model AS (
                SELECT
					a.id AS id,
					c.product_id AS product_id,
					c.nombre AS nombre,
					c.pos AS suma,
					c.lastdate
                FROM product_template a
                LEFT JOIN(select product_id AS product_id,name AS nombre,SUM(quantity) AS pos , MAX(create_date) AS lastdate from account_invoice_line group by product_id,name) c on c.product_id=a.id
                WHERE c.pos>0 and a.sale_ok = True
            )
        """)
my_report_model()