1
# -*- coding: utf-8 -*-
2
##############################################################################
4
# OpenERP, Open Source Management Solution
5
# Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
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.
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.
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/>.
20
##############################################################################
28
from osv import fields,osv
31
def _code_get(self, cr, uid, context={}):
32
acc_type_obj = self.pool.get('account.account.type')
33
ids = acc_type_obj.search(cr, uid, [])
34
res = acc_type_obj.read(cr, uid, ids, ['code', 'name'], context)
35
return [(r['code'], r['name']) for r in res]
38
class report_account_receivable(osv.osv):
39
_name = "report.account.receivable"
40
_description = "Receivable accounts"
43
'name': fields.char('Week of Year', size=7, readonly=True),
44
'type': fields.selection(_code_get, 'Account Type', required=True),
45
'balance':fields.float('Balance', readonly=True),
46
'debit':fields.float('Debit', readonly=True),
47
'credit':fields.float('Credit', readonly=True),
53
create or replace view report_account_receivable as (
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,
64
account_account a on (l.account_id=a.id)
68
to_char(date,'YYYY:IW'), a.type
70
report_account_receivable()
72
#a.type in ('receivable','payable')
73
class temp_range(osv.osv):
75
_description = 'A Temporary table used for Dashboard view'
78
'name' : fields.char('Range',size=64)
83
class report_aged_receivable(osv.osv):
84
_name = "report.aged.receivable"
85
_description = "Aged Receivable Till Today"
88
def __init__(self, pool, cr):
89
super(report_aged_receivable, self).__init__(pool, cr)
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
97
self.called = True # To make sure that init doesn't get called multiple times
99
res = super(report_aged_receivable, self).fields_view_get(cr, user, view_id, view_type, context, toolbar=toolbar, submenu=submenu)
102
def _calc_bal(self, cr, uid, ids, name, args, context):
104
for period in self.read(cr,uid,ids,['name']):
105
date1,date2 = period['name'].split(' to ')
106
cr.execute("SELECT SUM(credit-debit) FROM account_move_line AS line, account_account as ac \
107
WHERE (line.account_id=ac.id) AND ac.type='receivable' \
108
AND (COALESCE(line.date,date) BETWEEN %s AND %s) \
109
AND (reconcile_id IS NULL) AND ac.active",(str(date2),str(date1),))
110
amount = cr.fetchone()
111
amount = amount[0] or 0.00
112
res[period['id']] = amount
117
'name': fields.char('Month Range', size=7, readonly=True),
118
'balance': fields.function(_calc_bal, method=True, string='Balance', readonly=True),
121
def init(self, cr, uid=1):
122
""" This view will be used in dashboard
123
The reason writing this code here is, we need to check date range from today to first date of fiscal year.
125
# ranges = _get_ranges(cr) # Gets the ranges for the x axis of the graph (name column values)
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)
131
fy_start_date = pool_obj_fy.read(cr, uid, fy_id, ['date_start'])['date_start']
132
fy_start_date = mx.DateTime.strptime(fy_start_date, '%Y-%m-%d')
133
last_month_date = mx.DateTime.strptime(today, '%Y-%m-%d') - mx.DateTime.RelativeDateTime(months=1)
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- 1).strftime('%Y-%m-%d')
138
last_month_date = mx.DateTime.strptime(today, '%Y-%m-%d') - mx.DateTime.RelativeDateTime(months=1)
140
LIST_RANGES.append(today +" to " + fy_start_date.strftime('%Y-%m-%d'))
141
cr.execute('delete from temp_range')
143
for range in LIST_RANGES:
144
pooler.get_pool(cr.dbname).get('temp.range').create(cr, uid, {'name':range})
147
create or replace view report_aged_receivable as (
148
select id,name from temp_range
151
report_aged_receivable()
153
class report_invoice_created(osv.osv):
154
_name = "report.invoice.created"
155
_description = "Report of Invoices Created within Last 15 days"
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('Date Invoiced', readonly=True),
171
'date_due': fields.date('Due Date', readonly=True),
172
'residual': fields.float('Residual', readonly=True),
173
'state': fields.selection([
175
('proforma','Pro-forma'),
176
('proforma2','Pro-forma'),
179
('cancel','Cancelled')
180
],'State', readonly=True),
181
'origin': fields.char('Origin', size=64, readonly=True, help="Reference of the document that generated this invoice report."),
182
'create_date' : fields.datetime('Create Date', readonly=True)
184
_order = 'create_date'
187
cr.execute("""create or replace view report_invoice_created as (
189
inv.id as id, inv.name as name, inv.type as type,
190
inv.number as number, inv.partner_id as partner_id,
191
inv.amount_untaxed as amount_untaxed,
192
inv.amount_total as amount_total, inv.currency_id as currency_id,
193
inv.date_invoice as date_invoice, inv.date_due as date_due,
194
inv.residual as residual, inv.state as state,
195
inv.origin as origin, inv.create_date as create_date
199
(to_date(to_char(inv.create_date, 'YYYY-MM-dd'),'YYYY-MM-dd') <= CURRENT_DATE)
201
(to_date(to_char(inv.create_date, 'YYYY-MM-dd'),'YYYY-MM-dd') > (CURRENT_DATE-15))
203
report_invoice_created()
205
class report_account_type_sales(osv.osv):
206
_name = "report.account_type.sales"
207
_description = "Report of the Sales by Account Type"
210
'name': fields.char('Year',size=64,required=False, readonly=True),
211
'period_id': fields.many2one('account.period', 'Force Period',readonly=True),
212
'product_id': fields.many2one('product.product', 'Product',readonly=True),
213
'quantity': fields.float('Quantity', readonly=True),
214
'user_type': fields.many2one('account.account.type', 'Account Type', readonly=True),
215
'amount_total': fields.float('Total', readonly=True),
216
'currency_id': fields.many2one('res.currency', 'Currency', readonly=True),
217
'month':fields.selection([('01','January'), ('02','February'), ('03','March'), ('04','April'), ('05','May'), ('06','June'),
218
('07','July'), ('08','August'), ('09','September'), ('10','October'), ('11','November'), ('12','December')],'Month',readonly=True),
220
_order = 'name desc,amount_total desc'
223
tools.drop_view_if_exists(cr, 'report_account_type_sales')
224
cr.execute("""create or replace view report_account_type_sales as (
226
min(inv_line.id) as id,
227
to_char(inv.date_invoice, 'YYYY') as name,
228
to_char(inv.date_invoice,'MM') as month,
229
sum(inv_line.price_subtotal) as amount_total,
230
inv.currency_id as currency_id,
233
sum(inv_line.quantity) as quantity,
236
account_invoice_line inv_line
237
inner join account_invoice inv on inv.id = inv_line.invoice_id
238
inner join account_account account on account.id = inv_line.account_id
240
inv.state in ('open','paid')
242
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
report_account_type_sales()
247
class report_account_sales(osv.osv):
248
_name = "report.account.sales"
249
_description = "Report of the Sales by Account"
252
'name': fields.char('Year',size=64,required=False, readonly=True),
253
'period_id': fields.many2one('account.period', 'Force Period',readonly=True),
254
'product_id': fields.many2one('product.product', 'Product',readonly=True),
255
'quantity': fields.float('Quantity', readonly=True),
256
'account_id': fields.many2one('account.account', 'Account', readonly=True),
257
'amount_total': fields.float('Total', readonly=True),
258
'currency_id': fields.many2one('res.currency', 'Currency', readonly=True),
259
'month':fields.selection([('01','January'), ('02','February'), ('03','March'), ('04','April'), ('05','May'), ('06','June'),
260
('07','July'), ('08','August'), ('09','September'), ('10','October'), ('11','November'), ('12','December')],'Month',readonly=True),
262
_order = 'name desc,amount_total desc'
265
tools.drop_view_if_exists(cr, 'report_account_sales')
266
cr.execute("""create or replace view report_account_sales as (
268
min(inv_line.id) as id,
269
to_char(inv.date_invoice, 'YYYY') as name,
270
to_char(inv.date_invoice,'MM') as month,
271
sum(inv_line.price_subtotal) as amount_total,
272
inv.currency_id as currency_id,
275
sum(inv_line.quantity) as quantity,
276
account.id as account_id
278
account_invoice_line inv_line
279
inner join account_invoice inv on inv.id = inv_line.invoice_id
280
inner join account_account account on account.id = inv_line.account_id
282
inv.state in ('open','paid')
284
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
report_account_sales()
288
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: