# -*- coding: utf-8 -*-
###############################################################################
#
#    Tech-Receptives Solutions Pvt. Ltd.
#    Copyright (C) 2009-TODAY Tech-Receptives(<http://www.techreceptives.com>).
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU Lesser 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 Lesser General Public License for more details.
#
#    You should have received a copy of the GNU Lesser General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
###############################################################################

from odoo.osv import expression
from odoo.tools.float_utils import float_round as round
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT
from odoo.exceptions import UserError, ValidationError
from odoo import api, fields, models, _
from odoo import tools

import odoo.addons.decimal_precision as dp
			
class ViewEbusinessKpiTree(models.Model):
    _name = 'view.ebusiness.reports'
    _auto = False
    _rec_name = 'ebusiness_id'
    _order = 'state,ebusiness_id'
    _description = "View Ebusiness Reports"

    id = fields.Integer('Id')
    country_id = fields.Many2one('ecom.location', string='Country id')
    island_id = fields.Many2one('ecom.location', string='Island id', readonly=True)
    county_id = fields.Many2one('ecom.location', string='County id', readonly=True)
    parish_id = fields.Many2one('ecom.location', string='Parish id')
    ebusiness_type_id = fields.Many2one('ecom.ebusiness.type', 'Ebusiness type')
    partner_id = fields.Many2one('res.partner', string='Partner')
    type_id = fields.Many2one('ecom.ebusiness.type', string='Ebusiness type')
    enterprise_type_id = fields.Many2one('ecom.ebusiness.type', string='Enterprise type', readonly=True)
    site = fields.Char(string='Site')
    ebusiness_id = fields.Many2one('ecom.ebusiness', string='Ebusiness', readonly=True)
    sequence_code = fields.Char('Sequence Code', readonly=True)
    num_rooms = fields.Integer('Number of rooms', readonly=True)
    avg_price_rooms = fields.Float('Rooms Average Price', readonly=True)
    occupancy_rate = fields.Float('Ocuppancy Rate', readonly=True)
    market_share_rate = fields.Float('Market Share Rate', readonly=True)
    goal_rate = fields.Float('Goal Rate', readonly=True)
    contract_start_date = fields.Date('Contract start date', readonly=True)
    name_type_ebusiness = fields.Char(string='Name type ebusiness')
    name_type_enterprise = fields.Char(string='Name type enterprise')
    name_enterprise = fields.Char(string='Name Enterprise')
    comment = fields.Text(string='Description')
    phone = fields.Char(string='Phone contact')
    mobile = fields.Char(string='Fax contact')
    email = fields.Char(string='Email contact')
    nif = fields.Char(string='NIF')
    island = fields.Char(string='Island')
    county = fields.Char(string='County')
    parish = fields.Char(string='Parish')
    captcha_key = fields.Char(string='Captcha Key')
    api_key = fields.Char(string='API Key')
    policies = fields.Text(string='Policies')
    ebusiness_email = fields.Char(string='Email')
    ebusiness_phone = fields.Char(string='Phone')
    ebusiness_mobile = fields.Char(string='Mobile')
    state = fields.Selection([('draft', 'New'),
                              ('conceived', 'Conceived'),
                              ('approved', 'Approved'),
                              ('mounted', 'Mounted'),
                              ('inserted', 'Inserted'),('online', 'Online'),('cancel', 'Cancelled')], 'State', index=True)

    @api.model_cr
    def init(self):
        cr = self._cr
        tools.drop_view_if_exists(cr, 'view_ebusiness_reports')

        cr.execute("""
CREATE OR REPLACE VIEW view_ebusiness_reports AS (		
SELECT e.id, e.country_id, e.island_id, e.county_id, e.parish_id,eb.ebusiness_type_id, eb.partner_id, e.type_id,e.enterprise_type_id,eb.site 
,eb.id AS ebusiness_id,eb.num_rooms,eb.avg_price_rooms,eb.sequence_code,eb.occupancy_rate,eb.market_share_rate,eb.goal_rate,eb.contract_start_date
,te.name AS name_type_ebusiness,te2.name as name_type_enterprise
, e.name AS name_enterprise,e.comment
, ct.phone,ct.mobile, ct.email, ct.nif
, l.name AS island, l2.name AS county, l3.name AS parish,eb.captcha_key,eb.api_key,e.policies
,(select email from ecom_ebusiness_contact ebc where eb.id = ebc.ebusiness_id and ebc.default_contact is true limit 1) as ebusiness_email 
,(select phone from ecom_ebusiness_contact ebc where eb.id = ebc.ebusiness_id and ebc.default_contact is true limit 1) as ebusiness_phone 
,(select mobile from ecom_ebusiness_contact ebc where eb.id = ebc.ebusiness_id and ebc.default_contact is true limit 1) as ebusiness_mobile 
,eb.state
   FROM etourism_enterprise e
   LEFT JOIN ecom_ebusiness_type te ON e.type_id = te.id
   LEFT JOIN ecom_ebusiness_type te2 ON e.enterprise_type_id = te2.id
   LEFT JOIN ecom_ebusiness eb ON e.ebusiness_id = eb.id
   LEFT JOIN res_partner p ON eb.partner_id = p.id
   LEFT JOIN ecom_ebusiness_contact ct ON (ct.ebusiness_id = eb.id and ct.default_contact is true)
   LEFT JOIN ecom_location l ON e.island_id = l.id
   LEFT JOIN ecom_location l2 ON e.county_id = l2.id
   LEFT JOIN ecom_location l3 ON e.parish_id = l3.id
            )""")

class ViewGuestsReports(models.Model):
    _name = 'view.guests.reports'
    _auto = False
    _description = "View Guests Reports"

    id = fields.Integer('Id')
    name = fields.Char('Name', index=True)
    email = fields.Char('Email', index=True)
    phone = fields.Char('Phone', index=True)
    mobile = fields.Char('Mobile', index=True)
    country_id = fields.Many2one('ecom.location', string='Country id',index=True)
    gender = fields.Selection(
        [('m', 'Male'), ('f', 'Female')], 'Gender', index=True)
    birth_date = fields.Date('Birth Date', index=True)
    age = fields.Integer('Age', index=True)
    booking_group_id = fields.Many2one('etourism.booking.group', 'Booking Group',index=True)
    enterprise_id = fields.Many2one('etourism.enterprise', 'Enterprise',index=True)
    checkin = fields.Datetime(string='Checkin')
    checkout = fields.Datetime(string='Checkout')
    state = fields.Selection([('pending', 'Pending'),
                              ('confirmed', 'Confirmed'),
                              ('hosted', 'Checkin'),
                              ('checkout', 'Checkout'),
                              ('no_show', 'No Show'),
                              ('cancelled', 'Cancelled'),
                              ('request', 'Request')], 'State',index=False)

    @api.model_cr
    def init(self):
        cr = self._cr
        tools.drop_view_if_exists(cr, 'view_guests_reports')

        cr.execute("""
CREATE OR REPLACE VIEW view_guests_reports AS (		
select p.id,p.name,p.email,p.phone,p.mobile,p.country_id,p.gender,p.birth_date,extract(year from age(p.birth_date)) as age
,b.booking_group_id,b.enterprise_id,b.checkin,b.checkout,b.state
from
etourism_booking_res_partner_rel rel, etourism_booking b, res_partner p
where rel.partner_id=p.id and rel.booking_id=b.id
            )""")


class ViewBookingsReports(models.Model):
    _name = 'view.bookings.reports'
    _auto = False
    _description = "View Bookings Reports"

    id = fields.Integer('Id')
    checkin = fields.Datetime(string='Checkin')
    checkout = fields.Datetime(string='Checkout')
    duration = fields.Integer(string='Duration')
    state = fields.Selection([('pending', 'Pending'),
                              ('confirmed', 'Confirmed'),
                              ('hosted', 'Checkin'),
                              ('checkout', 'Checkout'),
                              ('no_show', 'No Show'),
                              ('cancelled', 'Cancelled'),
                              ('request', 'Request')], 'State')
    room_id = fields.Many2one('etourism.room', 'Room',index=True)
    enterprise_id = fields.Many2one('etourism.enterprise', 'Enterprise',index=True)
    booking_date = fields.Datetime('Booking Date', index=True)
    amount = fields.Float('Amount')
    booking_group_id = fields.Many2one('etourism.booking.group', 'Booking Group',index=True)
    booking_condition_id = fields.Many2one('etourism.booking.condition', string='Booking Condition',index=True)
    pricelist_item_id = fields.Many2one('product.pricelist.item', 'Pricelist Item',index=True)
    room_type_id = fields.Many2one('product.category', 'Room Type',index=True)
    reservation_no = fields.Char('Reservation No',index=True)

    @api.model_cr
    def init(self):
        cr = self._cr
        tools.drop_view_if_exists(cr, 'view_bookings_reports')

        cr.execute("""
CREATE OR REPLACE VIEW view_bookings_reports AS(	
select b.id,b.checkin,b.checkout,b.duration,b.state,b.room_id,b.enterprise_id,b.booking_date,b.amount
,b.booking_group_id,b.pricelist_item_id,b.booking_condition_id
,r.room_type_id,g.reservation_no
from etourism_booking b, etourism_booking_group g, etourism_room r
where g.id = b.booking_group_id and r.id = b.room_id
            )""")


        cr.execute("""
CREATE OR REPLACE FUNCTION total_amount_bookings(integer, date, varchar, varchar)
  RETURNS numeric AS
$BODY$ declare
	v_return numeric;
	v_booking RECORD ;
	v_booking2 RECORD ;
	total_amount numeric:=0; 
	total_discount numeric:=0; 
	v_type varchar;
 begin

	v_type:=$4;

	IF $3 = 'all'
	THEN
		FOR v_booking IN select b.id,b.reservation_no,b.num_rooms,pr.name as client_name,b.date_checkin,b.date_checkout,b.total_amount,b.state
	,(select count(*) from account_invoice_line l,account_invoice i,product_product p where l.invoice_id=i.id and l.product_id=p.id and p.isroom = true and i.booking_group_id=b.id) as num_invoices
	from etourism_booking_group b, res_partner pr
	where b.guest_id=pr.id and (b.date_booking=$2 or b.date_checkout=$2) and b.enterprise_id=$1
	order by b.checkin LOOP
			IF v_booking.num_invoices > 0
			THEN

				FOR v_booking2 IN select sum(l.price_subtotal) as total_amount,sum(discount) as total_discount
		from account_invoice_line l, account_invoice i,product_product p
		where i.id=l.invoice_id and p.id=l.product_id and p.isroom=true and i.booking_group_id=v_booking.id LOOP
					total_amount := total_amount + v_booking2.total_amount;
					total_discount := total_discount + v_booking2.total_discount;
				end loop;       
			ELSE
			    total_amount := total_amount + v_booking.total_amount;
			END IF;
		end loop;       

	ELSE
		FOR v_booking IN select b.id,b.reservation_no,b.num_rooms,pr.name as client_name,b.date_checkin,b.date_checkout,b.total_amount,b.state
	,(select count(*) from account_invoice_line l,account_invoice i,product_product p where l.invoice_id=i.id and l.product_id=p.id and p.isroom = true and i.booking_group_id=b.id) as num_invoices
	from etourism_booking_group b, res_partner pr
	where b.guest_id=pr.id and (b.date_booking=$2 or b.date_checkout=$2) and b.enterprise_id=$1 and b.state=$3
	order by b.checkin LOOP
			--RAISE NOTICE 'num invoices:: %',v_booking.num_invoices; 
			--RAISE NOTICE 'total amount:: %',v_booking.total_amount; 
			IF v_booking.num_invoices > 0
			THEN

				FOR v_booking2 IN select sum(l.price_subtotal) as total_amount,sum(discount) as total_discount
		from account_invoice_line l, account_invoice i,product_product p
		where i.id=l.invoice_id and p.id=l.product_id and p.isroom=true and i.booking_group_id=v_booking.id LOOP
					total_amount := total_amount + v_booking2.total_amount;
					total_discount := total_discount + v_booking2.total_discount;
				end loop;       
			ELSE
			    total_amount := total_amount + v_booking.total_amount;
			END IF;
		end loop;       
	END IF;
	
	IF v_type = 'discount' 
	THEN
		v_return:=total_discount;
	ELSE
		v_return:=total_amount; 
	END IF;

        return v_return;
 end;$BODY$
  LANGUAGE plpgsql VOLATILE
""")
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: