# -*- coding: utf-8 -*-
##############################################################################
#
#    OpenERP, Open Source Management Solution
#    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU Affero General Public License as
#    published by the Free Software Foundation, either version 3 of the
#    License, or (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU Affero General Public License for more details.
#
#    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 import tools
from openerp.osv import fields, osv

class seguir_pago(osv.osv):
    """
    Quants are the smallest unit of stock physical instances
    """
    _name = "seguir.pago"
    _description = "Seguirpago"
    _auto = False
    _columns = {
        'partner_id':fields.many2one('res.partner', 'Cliente', readonly=True),
        'ref': fields.char('Referencia', readonly=True),
        'date': fields.date('Fecha Factura', readonly=True),
		'debit': fields.float('Total', readonly=True),
        'debit_pyg': fields.float('Total', readonly=True),
		'date_maturity': fields.date('Vence', readonly=True),
        'currency_id':fields.many2one('res.currency', 'Moneda', readonly=True),
        'company_id':fields.many2one('res.company', 'Company', readonly=True),
        'rate': fields.float('Total', readonly=True),
        'pagado_usd': fields.float('pagado_usd', readonly=True, default=0),
        'saldo_usd': fields.float('saldo_usd', readonly=True, default=0),
        'pagado_pyg': fields.float('pagado_pyg', readonly=True, default=0),
        'saldo_pyg': fields.float('saldo_pyg', readonly=True, default=0),
    }
    _order = 'partner_id desc'

    def init(self, cr):
		tools.sql.drop_view_if_exists(cr, 'seguir_pago')
		cr.execute("""
            CREATE OR REPLACE VIEW seguir_pago as (
                SELECT	s.id as id,
	                    s.partner_id as partner_id,
	                    s.ref as ref,
	                    s.date as date,
	                    s.debit as debit,
	                   (CASE WHEN rrc.amount_tot is null THEN 0 ELSE rrc.amount_tot END) as pagado_usd,
                       (s.debit - CASE WHEN rrc.amount_tot is null THEN 0 ELSE rrc.amount_tot END ) as saldo_usd,
                       x.rate as rate,
                       (s.debit * x.rate) debit_pyg,
                       (CASE WHEN rrc.amount_tot is null THEN 0 ELSE rrc.amount_tot END * x.rate) as pagado_pyg,
                       ((s.debit * x.rate) - (CASE WHEN rrc.amount_tot is null THEN 0 ELSE rrc.amount_tot END * x.rate)) as saldo_pyg,
	                   s.date_maturity as date_maturity,
                       t.currency_id as currency_id,
	                   p.id as company_id
                FROM account_move_line s
	                   left join account_invoice t on (t.move_id=s.move_id)
                       left join res_currency d on (d.id=s.currency_id)
	                   left join res_partner r on (r.id=s.partner_id)
	                   left join res_company p on (p.id=s.company_id)
                       left join res_currency_rate x on (x.id =(select max(id)  from res_currency_rate where currency_id =d.id and currency_id=166))
                       left join (
		                           Select sum(avl.amount / rc.rate) as amount_tot, move_line_id
                                   from account_voucher_line avl
		                                 left join res_currency_rate rc on (rc.id = (SELECT max(rcr.id) FROM res_currency_rate rcr, account_voucher av ,account_journal aj
		                                       where av.id = avl.voucher_id AND rcr.currency_id = CASE WHEN aj.currency  is null THEN 3 ELSE aj.currency END AND av.journal_id = aj.id ))
		                          Group by move_line_id) as rrc on s.id = rrc.move_line_id
                where t.type='out_invoice' and  t.state<>'paid' and s.date_maturity IS NOT NULL AND s.reconcile_id is null
				GROUP BY s.id,s.partner_id,s.ref,s.date,s.date_maturity,p.id,t.currency_id,s.debit,x.rate, rrc.amount_tot)
		""")
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: