I have include the template top of page. While run the procedure results shown as template view. But when I export following option "PDF,EXCECL and CSV" it show the record set only. its not shown template format.
Here my procedure and query. If any this wrong please assist me.
DROP PROCEDURE IF EXISTS sp_DBR_Delivery_Tickets
$$
CREATE PROCEDURE sp_DBR_Delivery_Tickets(vCompanyid int,StartDate date,EndDate date,Salesperson text,StoreLocation int,pagedisplay int)
BEGIN
DROP TEMPORARY TABLE IF EXISTS tblonordpay;
CREATE TEMPORARY TABLE tblonordpay(id INT AUTO_INCREMENT PRIMARY KEY,orderid INT,balancedue decimal(10,2));
INSERT INTO tblonordpay(orderid,balancedue ) SELECT o.id,sum(pay.payment_amount) FROM ekidtv_cloudpos.tbl_orders AS o JOIN ekidtv_cloudpos.tbl_orderpayments as pay on pay.order_id=o.id and pay.company_id=o.company_id where o.company_id=vCompanyid group by pay.order_id;
/*SELECT * FROM tblonordpay;*/
IF(pagedisplay =1)THEN SELECT 'dbr.template', '#invoice'; select 'dbr.css', '.page-break.page {page-break-before:always} .page-break.page1 {page-break-before:auto}}'; select 'dbr.resultclass', 'page-break'; select 'dbr.wkhtmltopdf', "--header-html ''"; /* Do not use header in export using wkhtmltopdf */ select 'dbr.export.options', 'pagebreak', 1; select 'dbr.export.options', 'autosize', 0; /* Turn off autosize */
SELECT CONCAT_WS(char(13),CONCAT_WS(' ',o.shipfname,o.shiplname),o.shipaddress1,o.shipaddress2,CONCAT_WS(' ',o.shipcity,o.shipzip),o.shipcountries, CONCAT_WS(char(13),'')) AS CustomerName_Address1,GROUP_CONCAT(o.order_number SEPARATOR '####') AS InvoiceNumber1,GROUP_CONCAT(op.sku SEPARATOR '####') AS ModelNumber1,GROUP_CONCAT("" SEPARATOR '####') AS BookedSerials1,concat(s.first_name," ",s.last_name) AS SLMN1,op.Scheduled_Delivery_Date AS DatePromised1,sum(o.grandtotal-if(tmptbl.balancedue IS NULL,0,tmptbl.balancedue)) AS BalanceDue1,GROUP_CONCAT(st.name SEPARATOR '####') AS location1,GROUP_CONCAT(p.CommonBrandName SEPARATOR '####') AS prodcat,GROUP_CONCAT(op.qty SEPARATOR '####') AS qty,GROUP_CONCAT("" SEPARATOR '####')as assignedserials,site.sitename as companyname,o.grandtotal as grand,tmptbl.balancedue as balance,tmptbl.orderid as orderid,o.id as onumber FROM ekidtv_cloudpos.tbl_orders AS o JOIN ekidtv_cloudpos.tbl_orderdetails AS op on o.id=op.orders JOIN ekidtv_cloudpos.tbl_salesperson AS s on o.track_salesperson_id=s.id JOIN ekidtv_cloudpos.tbl_stores AS st on o.location=st.id JOIN ekidtv_cloudpos.tbl_clients AS c on o.client=c.id JOIN ekidtv_cloudpos.tbl_products AS p on p.id=op.productID JOIN ekidtv_cloudpos.Brand_Data AS b on b.pkID=p.pkBrand JOIN ekidtv_cloudpos.tbl_sitedetails as site on site.id=vCompanyid LEFT Join ekidtv_cloudpos.tblonordpay as tmptbl on tmptbl.orderid=o.id WHERE o.company_id=vCompanyid AND op.Date_Delivered IS NULL AND (op.Scheduled_Delivery_Date BETWEEN StartDate AND EndDate) AND IF(StoreLocation!=0,o.location=StoreLocation,0=0) AND IF(Salesperson!=0,o.track_salesperson_id IN (Salesperson),0=0) group by c.id,op.Scheduled_Delivery_Date ORDER BY op.Scheduled_Delivery_Date;
ELSE
DROP TEMPORARY TABLE IF EXISTS tblmodelserialized; CREATE TEMPORARY TABLE tblmodelserialized (id INT AUTO_INCREMENT PRIMARY KEY,order_id int,order_details_id int,productid int,serialized int,location_name varchar(20));
INSERT INTO tblmodelserialized (order_id,order_details_id,productid,serialized,location_name) SELECT o.id,od.id,od.productID,if(p.serialized is NULL,0,p.serialized),GROUP_CONCAT(if( (p.serialized is NULL OR p.serialized = 0),ns.name,ss.name)) AS location FROM tbl_orders as o Join tbl_orderdetails as od on o.id=od.orders Join ModelPrice as p ON (od.productID= p.product_id) LEFT JOIN tbl_serialnumbers as ser on (ser.order_id = od.orders AND od.productID = ser.product_id AND ser.order_details_id = od.id) LEFT JOIN tbl_nonserialized as nser on nser.order_id=od.orders AND od.productID = nser.product_id AND nser.order_details_id = od.id LEFT JOIN tbl_stores ns ON ns.id = nser.location LEFT JOIN tbl_stores ss ON ss.id = ser.location where o.company_id=1 AND od.is_sold = 2 AND if( (p.serialized is NULL OR p.serialized = 0),ns.name IS NOT NULL ,ss.name IS NOT NULL ) GROUP BY if( (p.serialized is NULL OR p.serialized = 0), nser.order_details_id ,ser .order_details_id );
SELECT site.sitename as companyname,op.Scheduled_Delivery_Date AS DatePromised1,IF(timeslots.start_time != "", CONCAT(start_time,'-',end_time),"") AS timeslots1,CONCAT_WS(char(13),CONCAT_WS(' ',o.shipfname,o.shiplname),o.shipaddress1,o.shipaddress2,CONCAT_WS(' ',o.shipcity,o.shipzip),o.shipcountries, CONCAT_WS(char(13),'')) AS CustomerName_Address1,GROUP_CONCAT(o.order_number SEPARATOR '####') AS InvoiceNumber1,site.phone,site.phone2,GROUP_CONCAT(op.sku SEPARATOR '####') AS ModelNumber1,GROUP_CONCAT(b.slug SEPARATOR '####') AS Brand1,GROUP_CONCAT(p.ModelDescription SEPARATOR '####') AS ModelDescription1,GROUP_CONCAT(op.qty SEPARATOR '####') AS qty1,GROUP_CONCAT(op.qty SEPARATOR '####') AS loaction1,sum(o.grandtotal-if(tmptbl.balancedue IS NULL,0,tmptbl.balancedue)) AS BalanceDue1,location_name,concat(s.first_name," ",s.last_name) AS SLMN1 FROM ekidtv_cloudpos.tbl_orders AS o JOIN ekidtv_cloudpos.tbl_orderdetails AS op on o.id=op.orders JOIN ekidtv_cloudpos.tbl_salesperson AS s on o.track_salesperson_id=s.id JOIN ekidtv_cloudpos.tbl_stores AS st on o.location=st.id JOIN ekidtv_cloudpos.tbl_clients AS c on o.client=c.id JOIN ekidtv_cloudpos.tbl_products AS p on p.id=op.productID JOIN ekidtv_cloudpos.Brand_Data AS b on b.pkID=p.pkBrand JOIN ekidtv_cloudpos.tbl_sitedetails as site on site.id=vCompanyid LEFT JOIN timeslots ON op.timeslot_id = timeslots.id LEFT Join ekidtv_cloudpos.tblonordpay as tmptbl on tmptbl.orderid=o.id LEFT JOIN tblmodelserialized as tmpmser ON tmpmser.order_details_id = op.id WHERE o.company_id=vCompanyid AND op.Date_Delivered IS NULL AND (op.Scheduled_Delivery_Date BETWEEN StartDate AND EndDate) AND IF(StoreLocation!=0,o.location=StoreLocation,0=0) AND IF(Salesperson!=0,o.track_salesperson_id IN (Salesperson),0=0) group by c.id,op.orders,op.Scheduled_Delivery_Date ORDER BY op.Scheduled_Delivery_Date;
END IF;
select 'dbr.purehtml', '<script type="text/javascript">$(document).ready(function() { var variable = $(".nsl").parent().css( "display", "none" ); $(".separator" ).each(function( index ) { var str = $(this).text(); str = str.replace(/####/g," "); $(this).html(str); }); });</script>'; select 'dbr.purehtml', '<style> @media print { hr {page-break-after:always} }
</style>';
END $$
