1
# -*- encoding: utf-8 -*-
2
##############################################################################
4
# OpenERP, Open Source Management Solution
5
# Copyright (C) 2004-2009 Tiny SPRL (<http://tiny.be>). All Rights Reserved
8
# This program is free software: you can redistribute it and/or modify
9
# it under the terms of the GNU General Public License as published by
10
# the Free Software Foundation, either version 3 of the License, or
11
# (at your option) any later version.
13
# This program is distributed in the hope that it will be useful,
14
# but WITHOUT ANY WARRANTY; without even the implied warranty of
15
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16
# GNU General Public License for more details.
18
# You should have received a copy of the GNU General Public License
19
# along with this program. If not, see <http://www.gnu.org/licenses/>.
21
##############################################################################
24
from osv import fields
27
#******************************************************************************************
29
#******************************************************************************************
30
class query_tool(osv.osv):
31
_inherit = 'query.tool'
37
# MOVE ALL ACCOUNT MOVE LINE FROM ONE PARTNER ID TO OTHER
39
DROP type IF EXISTS unify_account_partner_type CASCADE;
40
CREATE TYPE unify_account_partner_type AS (
50
CREATE OR REPLACE FUNCTION unify_account_partner(
53
) RETURNS SETOF unify_account_partner_type AS $$
55
r record ; pay record ; i integer := 0 ;
58
SELECT l.id AS lid, l.move_id AS pid, p.name AS partner, l.ref, m.name AS entry
59
FROM account_move_line l
60
LEFT JOIN account_move m ON (l.move_id = m.id)
61
LEFT JOIN res_partner p ON (l.partner_id = p.id)
62
WHERE l.partner_id = partner_from
65
RAISE NOTICE 'count : %,%,%,%,%', i, r.lid, r.partner, r.ref, r.entry;
66
UPDATE account_move_line SET partner_id = partner_to WHERE id = r.lid;
74
# CASH FLOW TAX REPORT
77
CREATE OR REPLACE FUNCTION get_tax_from_payments6(
82
order_by varchar) RETURNS SETOF varchar AS $$
84
all_accounts integer[] ;
86
all_total numeric[] := ARRAY[0,0,0,0] ;
91
doc_count integer := 0 ;
92
pay_count integer := 0 ;
93
reconcile integer := 0 ;
103
query_type varchar := '' ;
104
date_inv varchar := '' ;
105
result varchar := '';
107
entry varchar := '' ;
109
IF report_type = 'debit' THEN
110
query_type := ' AND l.debit > 0 ' ;
111
ELSIF report_type = 'credit' THEN
112
query_type := ' AND l.credit > 0 ' ;
115
FOR r IN EXECUTE 'SELECT id FROM account_account WHERE id IN '||tax
117
all_accounts := array_append(all_accounts, r.id) ;
121
WHILE all_accounts[indx] > 0
123
SELECT code INTO acc_code FROM account_account WHERE id = all_accounts[indx];
125
result := result||'"'||acc_code||'"';
126
all_total[4+indx] := 0 ;
128
result := result||',"'||acc_code||'"';
129
all_total[4+indx] := 0 ;
154
FOR r IN EXECUTE 'SELECT
156
SUM(l.debit) AS debit,
157
SUM(l.credit) AS credit,
159
MIN(a.code) AS account,
160
MIN(m.name) AS entry,
161
MIN(j.code) AS journal,
162
CASE WHEN MIN(partner.name) IS NULL THEN '||quote_literal('')||'
163
ELSE replace(MIN(partner.name), '||quote_literal('"')||', '||quote_literal('')||') END AS partner,
164
CASE WHEN MIN(partner.vat) IS NULL THEN '||quote_literal('')||' ELSE
165
replace(replace(replace(MIN(partner.vat), '||quote_literal('-')||','||quote_literal('')||'), '
166
||quote_literal(' ')||','||quote_literal('')||'), chr(10),'||quote_literal('')||') END AS vat
168
LEFT JOIN account_journal j ON (m.journal_id = j.id)
169
LEFT JOIN account_move_line l ON (l.move_id = m.id)
170
LEFT JOIN res_partner partner ON (l.partner_id = partner.id)
171
LEFT JOIN account_account a ON (l.account_id = a.id)
172
LEFT JOIN account_account_type t ON (a.user_type = t.id)
173
WHERE t.code = '||quote_literal('cash')
174
||' AND l.date BETWEEN '||quote_literal(date_start)||' AND '||quote_literal(date_stop)
175
--||' and m.id = 990 '
184
all_total[1] := all_total[1] + r.debit;
185
all_total[2] := all_total[2] + r.credit;
186
all_total[3] := all_total[3] + (r.debit - r.credit);
188
-- Search how much document reconciled whit payment
190
FOR r1 IN SELECT distinct(l.move_id) AS move_id
191
FROM account_move_line l LEFT JOIN account_journal j ON (l.journal_id = j.id)
192
WHERE l.reconcile_id IN (SELECT DISTINCT(reconcile_id) FROM account_move_line WHERE move_id = r.id)
195
all_moves := array_append(all_moves, r1.move_id) ;
198
doc_count := array_upper(all_moves,1);
200
IF doc_count IS NULL THEN
202
WHILE all_accounts[indx] > 0
204
SELECT CASE WHEN SUM(l.debit) - SUM(l.credit) IS NULL THEN 0
205
ELSE SUM(l.debit) - SUM(l.credit) END INTO tax_amount
206
FROM account_move_line l WHERE move_id = r.id AND account_id = all_accounts[indx];
207
IF result = '' THEN result := result||tax_amount; ELSE result := result||','||tax_amount; END IF;
208
all_total[4+indx] := all_total[4+indx] + tax_amount;
217
||'"'||r.journal||'",'
218
||'"'||r.partner||'",'
224
||'"'||r.account||'",'
227
||r.debit-r.credit||','
234
WHILE all_moves[indx_m] > 0
236
SELECT number, reference INTO r3 FROM account_invoice WHERE move_id = all_moves[indx_m];
238
SELECT m.name, MIN(m.date) AS date,
239
CASE WHEN SUM(l.debit)-SUM(l.credit) IS NULL THEN 0 ELSE SUM(l.debit)-SUM(l.credit) END AS amount
240
INTO r5 FROM account_move_line l LEFT JOIN account_account a ON (l.account_id = a.id)
241
LEFT JOIN account_move m ON (l.move_id = m.id)
242
WHERE move_id = all_moves[indx_m] AND a.type in ('payable','receivable') GROUP BY m.name;
245
WHILE all_accounts[indx] > 0
247
SELECT CASE WHEN SUM(l.debit) - SUM(l.credit) IS NULL THEN 0
248
ELSE SUM(l.debit) - SUM(l.credit) END INTO tax_amount
249
FROM account_move_line l WHERE move_id = all_moves[indx_m] AND account_id = all_accounts[indx];
251
IF result = '' THEN result := result||tax_amount; ELSE result := result||','||tax_amount; END IF;
252
all_total[4+indx] := all_total[4+indx] + tax_amount;
256
all_total[4] := all_total[4] + r5.amount;
265
||'"'||r.journal||'",'
266
||'"'||r.partner||'",'
269
||'"'||r3.number||'",'
270
||'"'||r3.reference||'",'
272
||'"'||r.account||'",'
275
||r.debit-r.credit||','
301
indx_m := indx_m + 1;
308
WHILE all_accounts[indx] > 0
311
result := result||''||all_total[4+indx]||'';
313
result := result||','||all_total[4+indx]||'';
318
--RAISE NOTICE 'result : %,%', all_total,result ;
346
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: