SELECT IFNULL((SELECT COUNT(*) FROM document_detail WHERE Labno=lt.Ledgertransactionno AND isactive=1),0) DocAttach,lt.LedgerTransactionID ,DATE_FORMAT(lt.`Date`,'%d-%b-%Y %h:%i %p') EntryDate,DATE_FORMAT(lt.`Date`,'%d-%b-%Y')RegDate , lt.`LedgerTransactionNo` LabNo,CONCAT(pm.`Title`,pm.`PName`) PName,CONCAT(pm.`Age`,'/',LEFT(pm.`Gender`,1)) Pinfo, pm.`Mobile`,lt.`DoctorName` DoctorName,fpm.Company_Name `PanelName`,lt.IsOPDConsultation, (SELECT centre FROM centre_master WHERE centreID=lt.centreid)CentreName, lt.CreatedBy UserName,lt.`GrossAmount`,lt.`DiscountOnTotal`,lt.`NetAmount`,lt.`Adjustment`,  GROUP_CONCAT(REPLACE(inv.Name,',',' ')) AS ItemName,          GROUP_CONCAT(IF(plo.Result_Flag=1 AND plo.Approved=0 ,'Y','N'))Result_Flag,     GROUP_CONCAT(IF(plo.Approved=1 AND plo.isPrint=0,'Y','N'))Approved,   GROUP_CONCAT(IF(plo.isPrint=1 AND plo.Approved=1 ,'Y','N'))ReportPrint,CASE WHEN GROUP_CONCAT(DISTINCT plo.BarcodeNo SEPARATOR ',<br/>') !='undefined' THEN GROUP_CONCAT(DISTINCT plo.BarcodeNo SEPARATOR ',<br/>') ELSE '' END BarcodeNo,  CASE     WHEN  (SELECT SUM(Amount) FROM patient_labinvestigation_opd WHERE  LedgerTransactionID = plo.`LedgerTransactionID`)=0 and plo.isactive=0 THEN '#6699ff'   WHEN (lt.NetAmount-lt.Adjustment)=0  AND lt.iscredit=0 THEN '#00FA9A'   WHEN (lt.iscredit =1) THEN '#F0FFF0'   WHEN  (lt.NetAmount-lt.Adjustment)>0 AND lt.Adjustment>0 AND lt.iscredit=0 THEN '#F6A9D1'   WHEN  (lt.NetAmount-lt.Adjustment)>0 AND lt.Adjustment=0 AND lt.iscredit=0 THEN '#FF457C'  ELSE '#F6A9D1'  END  rowColor,lt.Iscredit ,lt.IsDiscountApproved,lt.DiscountApprovedByID,IFNULL(lt.DiscountApprovedByName,'') DiscountApprovedByName,IFNULL(fpm.ReceiptType,'')ReceiptType,fpm.IsFullyPaid    ,(SELECT SUM(Amount) FROM patient_labinvestigation_opd WHERE  LedgerTransactionID = plo.`LedgerTransactionID`) Amount,(lt.NetAmount-lt.Adjustment)Due  FROM `f_ledgertransaction` lt  INNER JOIN `patient_labinvestigation_opd` plo ON lt.`LedgerTransactionID`=plo.`LedgerTransactionID`  AND plo.IsActive=1 INNER JOIN patient_master pm ON pm.`Patient_ID`=lt.`Patient_ID` INNER JOIN investigation_master inv ON inv.investigation_id = plo.investigation_id INNER JOIN f_panel_master fpm ON fpm.Panel_ID = lt.Panel_ID And lt.Panel_ID IN(SELECT Panel_ID FROM f_panel_master )  AND (lt.CentreID in ( SELECT DISTINCT `CentreAccess` FROM `centre_access` WHERE `CentreID`=@LoginCentre  ) or lt.CentreID=@LoginCentre )  AND plo.BarcodeNo =@LabNo GROUP BY lt.LedgerTransactionNo order by lt.date desc