Currently the code is as follows:
i have it set to still highlight the entire row, but the ultimate aim is to only have columns 4 onwards highlighted.
CREATE DEFINER=root@localhost PROCEDURE blah(
inStartDate date,
inEndDate date,
inVendor varchar(45),
inCurr varchar(3),
inRetailer varchar(45)
)
BEGIN
select 'dbr.export.options', 'orientation', 'landscape';
select 'dbr.export.options', 'autosize', 1;
select 'dbr.title', 'Price Scrapper';
SELECT 'dbr.hdr', 1, 2, 3;
select 'dbr.rowstyle', 8;
SELECT 'dbr.hidecolumn', 8;
SELECT VendorCode as Vendor, ps.cc_product_code as 'Product Code', Description, pr.retailer_name as 'Retailer', substr(max(datetime),1,10) as Date, value as 'Price (excl VAT)', psp.page_url AS URL, 0
FROM typ.tscrscrapes as ps
join typ.tpriceproducts as pp on code = ps.cc_product_code and active = 'Y'
join typ.tscrretailers as pr on ps.cis_account_code = pr.cis_account_code
left join typ.tscrproducts as psp on product_id = psp.id
WHERE CASE when (inVendor LIKE 'all vendors' ) then
VendorCode LIKE '%'
else
VendorCode LIKE CONCAT(inVendor, '%')
end
AND pr.cis_account_code LIKE IF(inRetailer IS NULL, '%', CONCAT(substr(inRetailer,1,6), '%'))
AND datetime between CONCAT(inStartDate, ' 00:00:00%') AND CONCAT(inEndDate, ' 23:59:59%')
AND value IS NOT NULL
AND jshop_currency_code like concat('%',inCurr,'%')
group by ps.cc_product_code, Description, pr.retailer_name, value, GBPRrp, GBPWebToday
union all
select distinct vendorcode, code, description, 'ColorConfidence', substr(sysdate('yyyy-mm-dd'),1,10),
CASE inCurr
WHEN 'GBP' THEN gbpwebtoday
WHEN 'EUR' THEN eurwebtoday
WHEN 'PLN' THEN plnwebtoday
ELSE gbpwebtoday
END as Price,
NULL, mydbr_style('Highlight Yellow')
from typ.tpriceproducts as prod
join typ.tscrscrapes as s on s.cc_product_code = code and datetime between CONCAT(inStartDate, ' 00:00:00%') AND CONCAT(inEndDate, ' 23:59:59%')
where CASE when (inVendor LIKE 'all vendors' ) then
VendorCode LIKE '%'
else
VendorCode LIKE CONCAT(inVendor, '%')
end
and active = 'Y'
order by 1,2,6,5,4;
END
