addons/account/report/account_report.py

   1 # -*- coding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
   6 #
   7 #    This program is free software: you can redistribute it and/or modify
   8 #    it under the terms of the GNU Affero General Public License as
   9 #    published by the Free Software Foundation, either version 3 of the
  10 #    License, or (at your option) any later version.
  11 #
  12 #    This program is distributed in the hope that it will be useful,
  13 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15 #    GNU Affero General Public License for more details.
  16 #
  17 #    You should have received a copy of the GNU Affero General Public License
  18 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  19 #
  20 ##############################################################################
  21
  22 import time
  23 from datetime import datetime
  24 from dateutil.relativedelta import relativedelta
  25
  26 from openerp import pooler
  27 from openerp import tools
  28 from openerp.osv import fields,osv
  29
  30 def _code_get(self, cr, uid, context=None):
  31     acc_type_obj = self.pool.get('account.account.type')
  32     ids = acc_type_obj.search(cr, uid, [])
  33     res = acc_type_obj.read(cr, uid, ids, ['code', 'name'], context)
  34     return [(r['code'], r['name']) for r in res]
  35
  36
  37 class report_account_receivable(osv.osv):
  38     _name = "report.account.receivable"
  39     _description = "Receivable accounts"
  40     _auto = False
  41     _columns = {
  42         'name': fields.char('Week of Year', size=7, readonly=True),
  43         'type': fields.selection(_code_get, 'Account Type', required=True),
  44         'balance':fields.float('Balance', readonly=True),
  45         'debit':fields.float('Debit', readonly=True),
  46         'credit':fields.float('Credit', readonly=True),
  47     }
  48     _order = 'name desc'
  49
  50     def init(self, cr):
  51         tools.drop_view_if_exists(cr, 'report_account_receivable')
  52         cr.execute("""
  53             create or replace view report_account_receivable as (
  54                 select
  55                     min(l.id) as id,
  56                     to_char(date,'YYYY:IW') as name,
  57                     sum(l.debit-l.credit) as balance,
  58                     sum(l.debit) as debit,
  59                     sum(l.credit) as credit,
  60                     a.type
  61                 from
  62                     account_move_line l
  63                 left join
  64                     account_account a on (l.account_id=a.id)
  65                 where
  66                     l.state <> 'draft'
  67                 group by
  68                     to_char(date,'YYYY:IW'), a.type
  69             )""")
  70 report_account_receivable()
  71
  72                     #a.type in ('receivable','payable')
  73 class temp_range(osv.osv):
  74     _name = 'temp.range'
  75     _description = 'A Temporary table used for Dashboard view'
  76
  77     _columns = {
  78         'name': fields.char('Range',size=64)
  79     }
  80
  81 temp_range()
  82
  83 class report_aged_receivable(osv.osv):
  84     _name = "report.aged.receivable"
  85     _description = "Aged Receivable Till Today"
  86     _auto = False
  87
  88     def __init__(self, pool, cr):
  89         super(report_aged_receivable, self).__init__(pool, cr)
  90         self.called = False
  91
  92     def fields_view_get(self, cr, user, view_id=None, view_type='form', context=None, toolbar=False, submenu=False):
  93         """ To call the init() method timely
  94         """
  95         if context is None:context = {}
  96         if not self.called:
  97             self.init(cr, user)
  98         self.called = True # To make sure that init doesn't get called multiple times
  99
 100         res = super(report_aged_receivable, self).fields_view_get(cr, user, view_id, view_type, context, toolbar=toolbar, submenu=submenu)
 101         return res
 102
 103     def _calc_bal(self, cr, uid, ids, name, args, context=None):
 104         res = {}
 105         for period in self.read(cr, uid, ids, ['name'], context=context):
 106             date1,date2 = period['name'].split(' to ')
 107             cr.execute("SELECT SUM(credit-debit) FROM account_move_line AS line, account_account as ac  \
 108                         WHERE (line.account_id=ac.id) AND ac.type='receivable' \
 109                             AND (COALESCE(line.date,date) BETWEEN %s AND  %s) \
 110                             AND (reconcile_id IS NULL) AND ac.active",(str(date2),str(date1),))
 111             amount = cr.fetchone()
 112             amount = amount[0] or 0.00
 113             res[period['id']] = amount
 114
 115         return res
 116
 117     _columns = {
 118         'name': fields.char('Month Range', size=7, readonly=True),
 119         'balance': fields.function(_calc_bal, string='Balance', readonly=True),
 120     }
 121
 122     def init(self, cr, uid=1):
 123         """ This view will be used in dashboard
 124         The reason writing this code here is, we need to check date range from today to first date of fiscal year.
 125         """
 126         pool_obj_fy = pooler.get_pool(cr.dbname).get('account.fiscalyear')
 127         today = time.strftime('%Y-%m-%d')
 128         fy_id = pool_obj_fy.find(cr, uid, exception=False)
 129         LIST_RANGES = []
 130         if fy_id:
 131             fy_start_date = pool_obj_fy.read(cr, uid, fy_id, ['date_start'])['date_start']
 132             fy_start_date = datetime.strptime(fy_start_date, '%Y-%m-%d')
 133             last_month_date = datetime.strptime(today, '%Y-%m-%d') - relativedelta(months=1)
 134
 135             while (last_month_date > fy_start_date):
 136                 LIST_RANGES.append(today + " to " + last_month_date.strftime('%Y-%m-%d'))
 137                 today = (last_month_date- relativedelta(days=1)).strftime('%Y-%m-%d')
 138                 last_month_date = datetime.strptime(today, '%Y-%m-%d') - relativedelta(months=1)
 139
 140             LIST_RANGES.append(today +" to " + fy_start_date.strftime('%Y-%m-%d'))
 141             cr.execute('delete from temp_range')
 142
 143             for range in LIST_RANGES:
 144                 pooler.get_pool(cr.dbname).get('temp.range').create(cr, uid, {'name':range})
 145
 146         cr.execute("""
 147             create or replace view report_aged_receivable as (
 148                 select id,name from temp_range
 149             )""")
 150
 151 report_aged_receivable()
 152
 153 class report_invoice_created(osv.osv):
 154     _name = "report.invoice.created"
 155     _description = "Report of Invoices Created within Last 15 days"
 156     _auto = False
 157     _columns = {
 158         'name': fields.char('Description', size=64, readonly=True),
 159         'type': fields.selection([
 160             ('out_invoice','Customer Invoice'),
 161             ('in_invoice','Supplier Invoice'),
 162             ('out_refund','Customer Refund'),
 163             ('in_refund','Supplier Refund'),
 164             ],'Type', readonly=True),
 165         'number': fields.char('Invoice Number', size=32, readonly=True),
 166         'partner_id': fields.many2one('res.partner', 'Partner', readonly=True),
 167         'amount_untaxed': fields.float('Untaxed', readonly=True),
 168         'amount_total': fields.float('Total', readonly=True),
 169         'currency_id': fields.many2one('res.currency', 'Currency', readonly=True),
 170         'date_invoice': fields.date('Invoice Date', readonly=True),
 171         'date_due': fields.date('Due Date', readonly=True),
 172         'residual': fields.float('Residual', readonly=True),
 173         'state': fields.selection([
 174             ('draft','Draft'),
 175             ('proforma','Pro-forma'),
 176             ('proforma2','Pro-forma'),
 177             ('open','Open'),
 178             ('paid','Done'),
 179             ('cancel','Cancelled')
 180         ],'Status', readonly=True),
 181         'origin': fields.char('Source Document', size=64, readonly=True, help="Reference of the document that generated this invoice report."),
 182         'create_date': fields.datetime('Create Date', readonly=True)
 183     }
 184     _order = 'create_date'
 185
 186     def init(self, cr):
 187         tools.drop_view_if_exists(cr, 'report_invoice_created')
 188         cr.execute("""create or replace view report_invoice_created as (
 189             select
 190                inv.id as id, inv.name as name, inv.type as type,
 191                inv.number as number, inv.partner_id as partner_id,
 192                inv.amount_untaxed as amount_untaxed,
 193                inv.amount_total as amount_total, inv.currency_id as currency_id,
 194                inv.date_invoice as date_invoice, inv.date_due as date_due,
 195                inv.residual as residual, inv.state as state,
 196                inv.origin as origin, inv.create_date as create_date
 197             from
 198                 account_invoice inv
 199             where
 200                 (to_date(to_char(inv.create_date, 'YYYY-MM-dd'),'YYYY-MM-dd') <= CURRENT_DATE)
 201                 AND
 202                 (to_date(to_char(inv.create_date, 'YYYY-MM-dd'),'YYYY-MM-dd') > (CURRENT_DATE-15))
 203             )""")
 204 report_invoice_created()
 205
 206 class report_account_type_sales(osv.osv):
 207     _name = "report.account_type.sales"
 208     _description = "Report of the Sales by Account Type"
 209     _auto = False
 210     _columns = {
 211         'name': fields.char('Year', size=64, required=False, readonly=True),
 212         'period_id': fields.many2one('account.period', 'Force Period', readonly=True),
 213         'product_id': fields.many2one('product.product', 'Product', readonly=True),
 214         'quantity': fields.float('Quantity', readonly=True),
 215         'user_type': fields.many2one('account.account.type', 'Account Type', readonly=True),
 216         'amount_total': fields.float('Total', readonly=True),
 217         'currency_id': fields.many2one('res.currency', 'Currency', readonly=True),
 218         'month':fields.selection([('01','January'), ('02','February'), ('03','March'), ('04','April'), ('05','May'), ('06','June'),
 219                                   ('07','July'), ('08','August'), ('09','September'), ('10','October'), ('11','November'), ('12','December')], 'Month', readonly=True),
 220     }
 221     _order = 'name desc,amount_total desc'
 222
 223     def init(self, cr):
 224         tools.drop_view_if_exists(cr, 'report_account_type_sales')
 225         cr.execute("""create or replace view report_account_type_sales as (
 226             select
 227                min(inv_line.id) as id,
 228                to_char(inv.date_invoice, 'YYYY') as name,
 229                to_char(inv.date_invoice,'MM') as month,
 230                sum(inv_line.price_subtotal) as amount_total,
 231                inv.currency_id as currency_id,
 232                inv.period_id,
 233                inv_line.product_id,
 234                sum(inv_line.quantity) as quantity,
 235                account.user_type
 236             from
 237                 account_invoice_line inv_line
 238             inner join account_invoice inv on inv.id = inv_line.invoice_id
 239             inner join account_account account on account.id = inv_line.account_id
 240             where
 241                 inv.state in ('open','paid')
 242             group by
 243                 to_char(inv.date_invoice, 'YYYY'),to_char(inv.date_invoice,'MM'),inv.currency_id, inv.period_id, inv_line.product_id, account.user_type
 244             )""")
 245 report_account_type_sales()
 246
 247
 248 class report_account_sales(osv.osv):
 249     _name = "report.account.sales"
 250     _description = "Report of the Sales by Account"
 251     _auto = False
 252     _columns = {
 253         'name': fields.char('Year', size=64, required=False, readonly=True, select=True),
 254         'period_id': fields.many2one('account.period', 'Force Period', readonly=True),
 255         'product_id': fields.many2one('product.product', 'Product', readonly=True),
 256         'quantity': fields.float('Quantity', readonly=True),
 257         'account_id': fields.many2one('account.account', 'Account', readonly=True),
 258         'amount_total': fields.float('Total', readonly=True),
 259         'currency_id': fields.many2one('res.currency', 'Currency', readonly=True),
 260         'month':fields.selection([('01','January'), ('02','February'), ('03','March'), ('04','April'), ('05','May'), ('06','June'),
 261                                   ('07','July'), ('08','August'), ('09','September'), ('10','October'), ('11','November'), ('12','December')], 'Month', readonly=True),
 262     }
 263     _order = 'name desc'
 264
 265     def init(self, cr):
 266         tools.drop_view_if_exists(cr, 'report_account_sales')
 267         cr.execute("""create or replace view report_account_sales as (
 268             select
 269                min(inv_line.id) as id,
 270                to_char(inv.date_invoice, 'YYYY') as name,
 271                to_char(inv.date_invoice,'MM') as month,
 272                sum(inv_line.price_subtotal) as amount_total,
 273                inv.currency_id as currency_id,
 274                inv.period_id,
 275                inv_line.product_id,
 276                sum(inv_line.quantity) as quantity,
 277                account.id as account_id
 278             from
 279                 account_invoice_line inv_line
 280             inner join account_invoice inv on inv.id = inv_line.invoice_id
 281             inner join account_account account on account.id = inv_line.account_id
 282             where
 283                 inv.state in ('open','paid')
 284             group by
 285                 to_char(inv.date_invoice, 'YYYY'),to_char(inv.date_invoice,'MM'),inv.currency_id, inv.period_id, inv_line.product_id, account.id
 286             )""")
 287 report_account_sales()
 288
 289 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: