now()))
left join product_price_details d1
on d1.product_price_id = pp.product_price_id and d1.name = "markup"
left join product_price_details d3
on d3.product_price_id = pp.product_price_id and d3.name = "cost"
left join product_price_classifications ppc on pp.product_price_id = ppc.product_price_id
left join lookups ppg on ppc.lookup_id = ppg.lookup_id and ppg.arch_short_name = "lookup.pricingGroup"
where e.name like concat(ifnull($P{Product Name},""),"%")
union
select pp.product_price_id as ppid, pp.price as price, e.entity_id as pid,
pp.start_time, pp.end_time, pp.name, ppg.name as ppgName, pt.entity_id as lkid,
if(pp.arch_short_name="productPrice.fixedPrice","L",if(pp.arch_short_name="productPrice.unitPrice","LU","?")) as type,
cast(d1.value as decimal(18.3)) as markup,
cast(d3.value as decimal(18,3)) as cost
from products p
join entities e on p.product_id = e.entity_id
join entity_relationships erl on erl.source_id = e.entity_id and erl.arch_short_name = 'entityRelationship.productLink'
join entities pt on pt.entity_id = erl.target_id
join product_prices pp on pt.entity_id = pp.product_id
and ((pp.start_time is null or pp.start_time <= now()) and (pp.end_time is null or pp.end_time > now()))
left join product_price_details d1
on d1.product_price_id = pp.product_price_id and d1.name = "markup"
left join product_price_details d3
on d3.product_price_id = pp.product_price_id and d3.name = "cost"
left join product_price_classifications ppc on pp.product_price_id = ppc.product_price_id
left join lookups ppg on ppc.lookup_id = ppg.lookup_id and ppg.arch_short_name = "lookup.pricingGroup"
where e.name like concat(ifnull($P{Product Name},""),"%")
) as pp on pp.pid = e.entity_id
where e.active = true
and ((substring($P{Product Name},1,1)="!" and ifnull(e.name,"--NONE--") not like concat(substring($P{Product Name},2),"%") ) or
( ifnull(e.name,"--NONE--") like concat(ifnull($P{Product Name},""),"%") )
)
and ((substring($P{Product Type},1,1)="!" and ifnull(pt.name,"--NONE--") not like concat(substring($P{Product Type},2),"%") ) or
( ifnull(pt.name,"--NONE--") like concat(ifnull($P{Product Type},""),"%") )
)
and ((substring($P{Pricing Group},1,1)="!" and ifnull(pp.ppgName,"--NONE--") not like concat(substring($P{Pricing Group},2),"%") ) or
( ifnull(pp.ppgName,"--NONE--") like concat(ifnull($P{Pricing Group},""),"%") )
)
and ((substring($P{Classification},1,1)="!" and ifnull(lc.name,"--NONE--") not like concat(substring($P{Classification},2),"%") ) or
( ifnull(lc.name,"--NONE--") like concat(ifnull($P{Classification},""),"%") )
)
and (
(e.arch_short_name = "product.service" and $P{IncServices}) or
(e.arch_short_name = "product.medication" and $P{IncMedications}) or
(e.arch_short_name = "product.merchandise" and $P{IncMerchandise})
)
and ((isnull(pp.markup) or (pp.markup >= $P{Low Markup} and pp.markup <= $P{High Markup})))
group by e.entity_id, pp.ppid
order by pt.name, e.name, pp.type, pp.name, pp.ppgName]]>
1]]>