--
-- +++ READ THIS +++
--
-- Sept. 2006 cognovis/nfl
-- projects_to_bill changed (and only that one!)
-- now it's getting the information about the sum from iv_offers.amount_open
-- instead of summing iv_offer_items
-- so there is no support if more items will be added and amount_open won't be updated
-- and there is no support for credit advices (Gutschriften)
--
select
sub.project_id,
sub.title,
sub.description,
sub.amount_open,
sub.creation_date,
sub.name,
lower(name) as name2,
sub.recipient_id,
sub.customer_id as org_id
from (select
oi.item_id as offer_id,
pi.item_id as project_id,
pr.title,
pr.description,
o.creation_date,
-- sum(ofi.item_units * ofi.price_per_unit * (1-(ofi.rebate/100))) as amount_open_old,
io.amount_total as amount_open,
p.customer_id,
p.recipient_id,
oz.name
from
cr_items pi,
cr_revisions pr,
pm_projects p,
acs_objects o,
acs_data_links r,
-- iv_offer_items ofi,
cr_items oi,
organizations oz,
iv_offers io
where
pi.latest_revision = pr.revision_id
and p.project_id = pr.revision_id
and o.object_id = p.project_id
and r.object_id_one = pi.item_id
and r.object_id_two = oi.item_id
and p.status_id = :p_closed_id
and p.invoice_p = true
-- and ofi.offer_id = oi.latest_revision
and p.customer_id = oz.organization_id
and io.offer_id = oi.latest_revision
group by
oi.item_id, pi.item_id, pr.title, pr.description, o.creation_date, p.customer_id, oz.name, p.recipient_id, io.amount_total) sub
where 1=1 [template::list::filter_where_clauses -and -name projects]
[template::list::page_where_clause -and -name projects -key sub.project_id]
[template::list::orderby_clause -name projects -orderby]
select
sub.project_id,
sub.title,
sub.description,
sub.amount_open,
sub.creation_date,
total.count_total,
billed.count_billed,
sub.name,
lower(name) as name2,
sub.recipient_id,
sub.customer_id as org_id
from (
select
oi.item_id as offer_id,
pi.item_id as project_id,
pr.title,
pr.description,
o.creation_date,
sum(ofi.item_units * ofi.price_per_unit * (1-(ofi.rebate/100))) as amount_open,
p.customer_id,
p.recipient_id,
oz.name
from
cr_items pi,
cr_revisions pr,
pm_projects p,
acs_objects o,
acs_data_links r,
iv_offer_items ofi,
cr_items oi,
organizations oz
where
pi.latest_revision = pr.revision_id
and p.project_id = pr.revision_id
and o.object_id = p.project_id
and r.object_id_one = pi.item_id
and r.object_id_two = oi.item_id
and p.status_id = :p_closed_id
and p.invoice_p = true
and ofi.offer_id = oi.latest_revision
and p.customer_id = oz.organization_id
group by
oi.item_id, pi.item_id, pr.title, pr.description, o.creation_date, p.customer_id, oz.name, p.recipient_id
) sub,
(
select
count(*) as count_total, oi.item_id
from
cr_items oi, iv_offer_items ofi
where
ofi.offer_id = oi.latest_revision
group by
oi.item_id
) total,
(
select
count(ci.item_id) as count_billed, oi.item_id
from
cr_items oi, iv_offer_items ofi
left outer join iv_invoice_items ii
on (ii.offer_item_id = ofi.offer_item_id)
left outer join iv_invoices i
on (ii.invoice_id = i.invoice_id and i.cancelled_p = 'f')
left outer join cr_items ci
on (ci.latest_revision = i.invoice_id)
where
ofi.offer_id = oi.latest_revision
group by
oi.item_id
) billed
where
total.item_id = sub.offer_id
and billed.item_id = sub.offer_id
[template::list::filter_where_clauses -and -name projects]
[template::list::page_where_clause -and -name projects -key sub.project_id]
[template::list::orderby_clause -name projects -orderby]
select
sub.project_id
from ( select pi.item_id as project_id,
pr.title,
pr.description,
p.customer_id,
p.recipient_id
from
cr_items pi,
cr_revisions pr,
pm_projects p
where
pi.latest_revision = pr.revision_id
$organization_where_clause
and p.project_id = pr.revision_id
and p.status_id = :p_closed_id
and p.invoice_p = true) sub,
acs_data_links r,
cr_items oi,
iv_offer_items ofi
where
r.object_id_one = sub.project_id
and r.object_id_two = oi.item_id
and ofi.offer_id = oi.latest_revision
and ofi.offer_item_id not in (select ii.offer_item_id
from iv_invoice_items ii, iv_invoices i, cr_items ci
where i.invoice_id = ii.invoice_id
and ci.latest_revision = i.invoice_id
and i.cancelled_p = 'f')
[template::list::filter_where_clauses -and -name projects]
group by
sub.project_id, sub.title, sub.description, sub.customer_id, sub.recipient_id, oi.name
[template::list::orderby_clause -name projects -orderby]