← Back to branch summary

~argilsoft/+junk/mexico-70

« back to all changes in this revision

Viewing changes to l10n_mx_invoice_datetime/patchs/account_invoice_report.sql

  • Committer: Israel Cruz Argil
  • Date: 2013-02-16 00:45:44 UTC
  • Revision ID: israel.cruz@hesatecnica.com-20130216004544-gc1gwon7509x5uyn
Initial Importing...

Show diffs side-by-side

added added

removed removed

Lines of Context:
l10n_mx_invoice_datetime/patchs/account_invoice_report.sql
 
1
DROP VIEW IF EXISTS account_invoice_report ;
 
2
 
 
3
create or replace view account_invoice_report as (
 
4
                 select min(ail.id) as id,
 
5
                    CAST( ai.date_invoice AS DATE) as date,
 
6
                    to_char(ai.date_invoice, 'YYYY') as year,
 
7
                    to_char(ai.date_invoice, 'MM') as month,
 
8
                    to_char(ai.date_invoice, 'YYYY-MM-DD') as day,
 
9
                    ail.product_id,
 
10
                    ai.partner_id as partner_id,
 
11
                    ai.payment_term as payment_term,
 
12
                    ai.period_id as period_id,
 
13
                    (case when u.uom_type not in ('reference') then
 
14
                        (select name from product_uom where uom_type='reference' and active and category_id=u.category_id LIMIT 1)
 
15
                    else
 
16
                        u.name
 
17
                    end) as uom_name,
 
18
                    ai.currency_id as currency_id,
 
19
                    ai.journal_id as journal_id,
 
20
                    ai.fiscal_position as fiscal_position,
 
21
                    ai.user_id as user_id,
 
22
                    ai.company_id as company_id,
 
23
                    count(ail.*) as nbr,
 
24
                    ai.type as type,
 
25
                    ai.state,
 
26
                    pt.categ_id,
 
27
                    ai.date_due as date_due,
 
28
                    ai.address_contact_id as address_contact_id,
 
29
                    ai.address_invoice_id as address_invoice_id,
 
30
                    ai.account_id as account_id,
 
31
                    ai.partner_bank_id as partner_bank_id,
 
32
                    sum(case when ai.type in ('out_refund','in_invoice') then
 
33
                         ail.quantity / u.factor * -1
 
34
                        else
 
35
                         ail.quantity / u.factor
 
36
                        end) as product_qty,
 
37
                    sum(case when ai.type in ('out_refund','in_invoice') then
 
38
                         ail.quantity*ail.price_unit * -1
 
39
                        else
 
40
                         ail.quantity*ail.price_unit
 
41
                        end) / cr.rate as price_total,
 
42
                    sum(case when ai.type in ('out_refund','in_invoice') then
 
43
                         ai.amount_total * -1
 
44
                        else
 
45
                         ai.amount_total
 
46
                         end) / (CASE WHEN 
 
47
                              (select count(l.id) from account_invoice_line as l
 
48
                               left join account_invoice as a ON (a.id=l.invoice_id)
 
49
                               where a.id=ai.id) <> 0 
 
50
                            THEN 
 
51
                              (select count(l.id) from account_invoice_line as l
 
52
                               left join account_invoice as a ON (a.id=l.invoice_id)
 
53
                               where a.id=ai.id) 
 
54
                            ELSE 1 
 
55
                            END) / cr.rate as price_total_tax,
 
56
                    (case when ai.type in ('out_refund','in_invoice') then
 
57
                      sum(ail.quantity*ail.price_unit*-1)
 
58
                    else
 
59
                      sum(ail.quantity*ail.price_unit)
 
60
                    end) / (CASE WHEN
 
61
                         (case when ai.type in ('out_refund','in_invoice') 
 
62
                          then sum(ail.quantity/u.factor*-1)
 
63
                          else sum(ail.quantity/u.factor) end) <> 0 
 
64
                       THEN 
 
65
                         (case when ai.type in ('out_refund','in_invoice') 
 
66
                          then sum(ail.quantity/u.factor*-1)
 
67
                          else sum(ail.quantity/u.factor) end) 
 
68
                       ELSE 1 
 
69
                       END)
 
70
                     / cr.rate as price_average,
 
71
 
 
72
                    cr.rate as currency_rate,
 
73
                    sum((select extract(epoch from avg(date_trunc('day',aml.date_created)-date_trunc('day',l.create_date)))/(24*60*60)::decimal(16,2)
 
74
                        from account_move_line as aml
 
75
                        left join account_invoice as a ON (a.move_id=aml.move_id)
 
76
                        left join account_invoice_line as l ON (a.id=l.invoice_id)
 
77
                        where a.id=ai.id)) as delay_to_pay,
 
78
                    sum((select extract(epoch from avg(date_trunc('day',a.date_due)-date_trunc('day',a.date_invoice)))/(24*60*60)::decimal(16,2)
 
79
                        from account_move_line as aml
 
80
                        left join account_invoice as a ON (a.move_id=aml.move_id)
 
81
                        left join account_invoice_line as l ON (a.id=l.invoice_id)
 
82
                        where a.id=ai.id)) as due_delay,
 
83
                    (case when ai.type in ('out_refund','in_invoice') then
 
84
                      ai.residual * -1
 
85
                    else
 
86
                      ai.residual
 
87
                    end)/ (CASE WHEN 
 
88
                        (select count(l.id) from account_invoice_line as l
 
89
                         left join account_invoice as a ON (a.id=l.invoice_id)
 
90
                         where a.id=ai.id) <> 0 
 
91
                       THEN
 
92
                        (select count(l.id) from account_invoice_line as l
 
93
                         left join account_invoice as a ON (a.id=l.invoice_id)
 
94
                         where a.id=ai.id) 
 
95
                       ELSE 1 
 
96
                       END) / cr.rate as residual
 
97
                from account_invoice_line as ail
 
98
                left join account_invoice as ai ON (ai.id=ail.invoice_id)
 
99
                left join product_template pt on (pt.id=ail.product_id)
 
100
                left join product_uom u on (u.id=ail.uos_id),
 
101
                res_currency_rate cr
 
102
                where cr.id in (select id from res_currency_rate cr2  where (cr2.currency_id = ai.currency_id)
 
103
                and ((ai.date_invoice is not null and cr.name <= ai.date_invoice) or (ai.date_invoice is null and cr.name <= NOW())) limit 1)
 
104
                group by ail.product_id,
 
105
                    ai.date_invoice,
 
106
                    ai.id,
 
107
                    cr.rate,
 
108
                    to_char(ai.date_invoice, 'YYYY'),
 
109
                    to_char(ai.date_invoice, 'MM'),
 
110
                    to_char(ai.date_invoice, 'YYYY-MM-DD'),
 
111
                    ai.partner_id,
 
112
                    ai.payment_term,
 
113
                    ai.period_id,
 
114
                    u.name,
 
115
                    ai.currency_id,
 
116
                    ai.journal_id,
 
117
                    ai.fiscal_position,
 
118
                    ai.user_id,
 
119
                    ai.company_id,
 
120
                    ai.type,
 
121
                    ai.state,
 
122
                    pt.categ_id,
 
123
                    ai.date_due,
 
124
                    ai.address_contact_id,
 
125
                    ai.address_invoice_id,
 
126
                    ai.account_id,
 
127
                    ai.partner_bank_id,
 
128
                    ai.residual,
 
129
                    ai.amount_total,
 
130
                    u.uom_type,
 
131
                    u.category_id
 
132
            )
 
133
;
 
 
b'\\ No newline at end of file'