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 ##############################################################################
23 from datetime import datetime
24 from dateutil.relativedelta import relativedelta
26 from openerp import pooler
27 from openerp import tools
28 from openerp.osv import fields,osv
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]
37 class report_account_receivable(osv.osv):
38 _name = "report.account.receivable"
39 _description = "Receivable accounts"
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),
51 tools.drop_view_if_exists(cr, 'report_account_receivable')
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
95 if context is None:context = {}
98 self.called = True # To make sure that init doesn't get called multiple times
100 res = super(report_aged_receivable, self).fields_view_get(cr, user, view_id, view_type, context, toolbar=toolbar, submenu=submenu)
103 def _calc_bal(self, cr, uid, ids, name, args, context=None):
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
118 'name': fields.char('Month Range', size=7, readonly=True),
119 'balance': fields.function(_calc_bal, string='Balance', readonly=True),
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.
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 = datetime.strptime(fy_start_date, '%Y-%m-%d')
133 last_month_date = datetime.strptime(today, '%Y-%m-%d') - relativedelta(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- relativedelta(days=1)).strftime('%Y-%m-%d')
138 last_month_date = datetime.strptime(today, '%Y-%m-%d') - relativedelta(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('Invoice Date', 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 ],'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)
184 _order = 'create_date'
187 tools.drop_view_if_exists(cr, 'report_invoice_created')
188 cr.execute("""create or replace view report_invoice_created as (
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
200 (to_date(to_char(inv.create_date, 'YYYY-MM-dd'),'YYYY-MM-dd') <= CURRENT_DATE)
202 (to_date(to_char(inv.create_date, 'YYYY-MM-dd'),'YYYY-MM-dd') > (CURRENT_DATE-15))
204 report_invoice_created()
206 class report_account_type_sales(osv.osv):
207 _name = "report.account_type.sales"
208 _description = "Report of the Sales by Account Type"
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),
221 _order = 'name desc,amount_total desc'
224 tools.drop_view_if_exists(cr, 'report_account_type_sales')
225 cr.execute("""create or replace view report_account_type_sales as (
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,
234 sum(inv_line.quantity) as quantity,
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
241 inv.state in ('open','paid')
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
245 report_account_type_sales()
248 class report_account_sales(osv.osv):
249 _name = "report.account.sales"
250 _description = "Report of the Sales by Account"
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),
266 tools.drop_view_if_exists(cr, 'report_account_sales')
267 cr.execute("""create or replace view report_account_sales as (
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,
276 sum(inv_line.quantity) as quantity,
277 account.id as account_id
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
283 inv.state in ('open','paid')
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
287 report_account_sales()
289 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: