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