sale_report_partner/sale_report_partner.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 sale_partner_model(osv.osv):
    _name = "sale.partner.model"
    _description = "Listado de Clientes"
    _auto = False
    _columns = {
		'partner_id': fields.many2one('res.partner', string='Cliente', required=True, readonly=True),
		'name': fields.char('Nombre', readonly=True),
		'street': fields.char('Direccion', readonly=True),
		'email': fields.char('Email', readonly=True),
		'mobile': fields.char('Celular', readonly=True),
		'phone': fields.char('Telefono', readonly=True),
		'total_facturado': fields.float('Facturado', readonly=True),
		'user_id':fields.many2one('res.users', 'Vendedor', readonly=True),
		'date': fields.datetime('Fecha', readonly=True),
    }
    _order = 'total_facturado asc'

    def init(self, cr):
        tools.sql.drop_view_if_exists(cr, 'sale_partner_model')
        cr.execute("""
            CREATE OR REPLACE VIEW sale_partner_model AS (
                SELECT 	a.id AS id,
						c.partner_id AS partner_id,
						a.name,
						a.street AS street,
						a.email AS email,
						a.mobile AS mobile,
						a.phone AS phone,
						c.price_total AS total_facturado,
						p.user_id as user_id,
						c.date AS date
				FROM res_partner a
                left join sale_order p on p.partner_id=a.id
				LEFT JOIN(select s.partner_id AS partner_id, sum(l.product_uom_qty * l.price_unit) as price_total, MAX(l.create_date) AS date 
							from sale_order_line as l
							left join sale_order s on s.id=l.order_id
							group by partner_id) c on c.partner_id=a.id
				WHERE a.customer= True and c.price_total>0
            )
        """)
sale_partner_model()