my_report_partner/my_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 my_partner_model(osv.osv):
    _name = "my.partner.model"
    _description = "Listado de ultima venta en pos del 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),
        'company_id':fields.many2one('res.company', 'Compania', readonly=True),
    }
    _order = 'date asc'

    def init(self, cr):
        tools.sql.drop_view_if_exists(cr, 'my_partner_model')
        cr.execute("""
            CREATE OR REPLACE VIEW my_partner_model AS (
                SELECT row_number() over (ORDER BY 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,
                lls.user_id as user_id,
                c.date AS date,
                c.company_id AS company_id
                FROM res_partner a
                left join pos_order p on p.partner_id=a.id
                LEFT JOIN( select pa.id AS partner_id, sum(l.qty * l.price_unit) as price_total, MAX(lp.create_date) AS date, lp.company_id AS company_id
                from pos_order_line as l
                left join pos_order lp on lp.id=l.order_id
                left join res_partner pa on (pa.id=lp.partner_id)
                group by pa.id,lp.company_id ) c on (c.partner_id = a.id)
                --usuraio
                left join ( SELECT sl.user_id, sl.partner_id, sl.company_id  FROM pos_order sl
                left join pos_order so on (so.create_date = (SELECT MAX(sll.create_date) as date
                FROM pos_order sll  WHERE sll.partner_id=sl.partner_id  group by sll.partner_id))
                where  sl.create_date =so.create_date
                group by sl.user_id, sl.partner_id, sl.company_id) lls on (lls.partner_id = a.id)
                WHERE a.customer= True and c.price_total>0
                GROUP BY a.id, c.partner_id, a.name, a.street, a.email, a.mobile, a.phone, c.price_total, lls.user_id, c.date, c.company_id
                order by c.partner_id
            )
        """)
my_partner_model()