SELECT * FROM (SELECT    t.ItemId, t.Password_Web,  t.LabNo,t.PLOID,t.laboutsrcid,t.FieldExe,t.InterpretationID,t.InterpretationType,      substr(replace(t.PName,'.','. '),1,35)PName, t.Age, t.BarcodeNo, t.Gender,t.ReportStatus,t.OtherReferLab,t.Interface_companyName,t.CorporateIDCard,t.PatientIDProofNo,t.PatientIDProof,t.LedgerTransactionNo_Interface,      t.DateEnrolled, t.PassPortNo,t.PureHealthID,t.Nationality,t.SRFNo, t.Patient_ID, t.Description, t.SampleBySelf, t.LabObservation_ID, t.Test_ID,      t.ResultDate,  t.ResultEnteredBy ,t.ForwardBy,           t.Value,t.`MinValue`,t.`MaxValue`, IF(t.flag='','',CASE WHEN t.flag='High' THEN 'H' WHEN t.flag='Low' THEN 'L' ELSE ' ' END)  flag,t.abnormalimage ,t.samplename,IF(im.printHeader=0 || t.IsCommentField=1,CONCAT('<div style=''font-weight:bold;''>',t.LabObservationName,'</div>'),IF(t.IsBold='1' && t.isunderline='1',CONCAT('<div style= ''padding-left:0px;text-decoration: underline;font-weight:bold;''>',t.LabObservationName,'</div>'),IF(t.isbold='1',CONCAT('<div style= ''padding-left:0px;font-weight:bold;''>',t.LabObservationName,'</div>'), IF(t.isunderline='1' ,CONCAT('<div style= ''padding-left:0px;text-decoration: underline;''>',t.LabObservationName,'</div>'),CONCAT('<div style= ''padding-left:0px;''>',t.LabObservationName,'</div>')))))LabObservationName,t.Investigation_ID, t.SlideNumber,t.TestCentreID,     t.SampleDate,     t.ReceiveDate,     t.DATE,           t.Approved,t.comments, t.MYApproved,     t.MYApprovedBy,t.ApprovedDate,     t.ApprovedBy, t.ResultEnteredDate,     t.ResultEnteredName, t.Unit,t.Child_Flag,     t.labPriorty,t.DisplayReading,t.PackageName, t.IsPackage,    t.DispatchMode,     t.ObsInterpretation ,     t.MethodName,t.SegratedDate,t.PrintDate,t.IsCommentField,t.IsOrganism,  t.OrganismID,t.AbnormalValue,t.IsBold,t.IsUnderLine,t.`IsMic`,t.SepratePrint,t.ShowDeltaReport,t.isborder,t.MachineID,  t.centreid,t.Panel_ID,t.ReferDoctor,left(pnl.Company_Name,30)Company_Name ,t.Company_code,t.Company_add,t.`HLMPatientType`,t.`HLMOPDIPDNo`,t.IsSampleCollectedByPatient,t.PatientType,  '' AS Interpretation,'1' ReportType,CONCAT(IF(t.IsPackage=1,'',''),  IF(IM.PrintSampleName=0,IF(IFNULL(itm.Inv_ShortName,'')!='',IM.Name,IM.Name),im.name)) AS TestName,im.PrintTestCentre,im.method,im.FileLimitationName, scm.deptinterpretaion,scm.ReportLine1,scm.ReportLine2,(select Address from centre_master where CentreId=t.TestCentreID)TCentreAdd, otm.Name AS Dept,otm.isVisible AS DeptVisible,    IM.Print_sequence AS Print_Sequence_Investigation,     im.PrintSeparate,im.printsamplename,'1' ShowInReport,  IM.Description AS d1,t.labPriorty Priorty,IF(VALUE ='HEAD' ,'Y','N') IsParent,im.printHeader  AS invHeader ,  '' SampleTypeID,cma.Centre CentreName,cma.Address,cma.Landline CentreLandline,cma.Mobile CentreMobile,cma.Email  CentreEmail,ifnull((select NablLogoPath from centre_master where centreID=t.TestCentreID),'')NablLogoPath,ifnull((select CapLogoPath from centre_master where centreID=t.TestCentreID),'')CapLogoPath,ifnull((select NabhLogoPath from centre_master where centreID=t.TestCentreID),'')NabhLogoPath,'' printOrder ,cma.type1,  IFNULL((SELECT isNABL FROM `investiagtion_isnabl` WHERE Investigation_ID=im.Investigation_Id AND centreid=t.TestCentreID LIMIT 1),0)isNABL,  otm.Print_Sequence Print_Sequence_Dept,IFNULL(t.labPriorty,9999)printOrder_labObservation1,labPriorty printOrder_labObservation2   ,otm.ObservationType_ID,pnl.`Panel_Code`,  pnl.ReportHeaderHeight,pnl.ReportHeaderXPosition,pnl.ReportHeaderYPosition,pnl.ReportFooterHeight,pnl.ReportBackGroundImage, pnl.AAALogo,pnl.TagProcessingLabID,IFNULL(LEFT(Add1,30),'')ClientAddress  FROM (       SELECT    pli.ItemId, lt.Password_Web,  pli.LedgerTransactionNo AS LabNo,pli.test_id PLOID,pli.laboutsrcid,IF(lt.HomeVisitBoyID='N/A','N/A',(SELECT  flm.NAME FROM feildboy_master flm WHERE flm.FeildBoyID=lt.HomeVisitBoyID)) FieldExe,pli.InterpretationID,pli.InterpretationType, CASE WHEN lt.Ismask > 0 THEN PMV.Paitent_name  ELSE Replace(lt.PName,'Master','Master ') END as PName , lt.Age,pli.BarcodeNo,left(lt.Gender,1)Gender,IF(pli.Approved=0,'Provisional Report','Final Report')ReportStatus,lt.OtherReferLab,lt.Interface_companyName,lt.CorporateIDCard,lt.PatientIDProofNo,lt.PatientIDProof,lt.LedgerTransactionNo_Interface,  DATE_FORMAT(pli.date,'%d/%b/%Y %I:%i%p') DateEnrolled,lt.PassPortNo,lt.PureHealthID,lt.Nationality,lt.SRFNo,lt.Patient_ID,pl.Description,pli.SampleBySelf,pl.LabObservation_ID,pl.Test_ID,  DATE_FORMAT(pli.ResultEnteredDate,'%d-%b-%y')AS ResultDate, pli.ResultEnteredBy,if(pli.ForwardBy=pli.ApprovedBy,'',pli.ForwardBy) ForwardBy, IF(pl.IsCommentField =1,'',IF(IsNumeric(pl.Value)=1 && lm.RoundUpto <>-1,CONVERT(FORMAT(TRIM(pl.Value) ,lm.RoundUpto) USING utf8),pl.Value))VALUE,pl.`MinValue`,pl.`MaxValue`, pl.`Flag`,pl.abnormalimage,pli.SampleTypeName samplename,pl.LabObservationName,pli.Investigation_ID, pli.SlideNumber,pli.TestCentreID,       DATE_FORMAT(pli.SampleCollectionDate,'%d/%b/%Y %I:%i%p')SampleDate,  DATE_FORMAT(pli.SampleReceiveDate,'%d/%b/%Y  %I:%i%p')ReceiveDate,  DATE_FORMAT(pli.Date,'%d-%b-%y %h:%i %p')DATE,        IF(pli.Approved=1,1,0)Approved,(SELECT comments FROM patient_labinvestigation_opd_comments ploc WHERE ploc.Test_ID = pli.Test_ID)comments,    IF(pli.isHold=1,'Hold',    IF(pli.Approved=1,'Approved',IF(pli.isForward=1,'Forward',''))) AS MYApproved,   IF(pli.isHold=1,pli.HoldByName,IF(pli.Approved=1,'',IF(pli.isForward=1,pli.ForwardByName,'')))    AS MYApprovedBy,       DATE_FORMAT(pli.ApprovedDate,'%d/%b/%Y %I:%i%p')ApprovedDate,   pli.ApprovedBy,        DATE_FORMAT(pli.ResultEnteredDate,'%d/%b/%Y %I:%i%p')ResultEnteredDate,    pli.ResultEnteredName,        pl.ReadingFormat AS Unit,lm.Child_Flag,    pl.Priorty labPriorty,REPLACE(IFNULL(pl.DisplayReading,''),'
','<br />')DisplayReading,if(pli.IsPackage=1,pli.PackageName,'') PackageName, pli.IsPackage,   '' DispatchMode    ,'' AS ObsInterpretation ,  IF(pl.PrintMethod='1' AND pl.Method <>'' , CONCAT('',pl.Method),'') MethodName,DATE_FORMAT(pli.sampleCollectiondate,'%d/%b/%Y %I:%i%p')SegratedDate,DATE_FORMAT(NOW(),'%d/%b/%Y %I:%i%p')PrintDate,pl.IsCommentField ,pl.IsOrganism, pl.OrganismID,pl.AbnormalValue,pl.IsBold,pl.IsUnderLine,pl.`IsMic`, pl.SepratePrint,pl.ShowDeltaReport,   (SELECT isborder FROM labobservation_investigation WHERE investigation_id=pli.investigation_id AND LabObservation_ID=pl.LabObservation_ID) isborder,pl.MachineID  , lt.centreid,lt.Panel_ID,Left(Concat('Dr.',UPPER(lt.`DoctorName`)),28) ReferDoctor, IF(lt.OtherReferLab='',left(lt.panelname,25),lt.OtherReferLab)  Company_Name ,'' Company_code,'' Company_add,lt.`HLMPatientType`,lt.`HLMOPDIPDNo`,pli.IsSampleCollectedByPatient,IFNULL(lt.PatientType,'')PatientType   FROM patient_labobservation_opd pl      INNER JOIN patient_labinvestigation_opd pli ON pli.Test_ID = pl.Test_ID AND TRIM(pl.value)<>'' AND pli.ReportType='1'   AND pli.Test_ID IN ({0})  AND pli.Result_Flag = 1 AND pli.ishold=0   INNER JOIN f_ledgertransaction lt ON lt.LedgerTransactionID=pli.LedgerTransactionID   INNER JOIN labobservation_master lm ON lm.LabObservation_ID = pl.LabObservation_ID and lm.PrintInLabReport='1'   LEFT JOIN Patient_master_VIP PMV on PMV.Paitent_ID=lt.Patient_ID       )t   INNER JOIN investigation_master IM ON IM.Investigation_Id = t.Investigation_ID    INNER JOIN f_itemmaster itm ON itm.Type_ID = IM.Investigation_ID      INNER JOIN `f_subcategorymaster` scm ON scm.`SubCategoryID`=itm.`SubCategoryID` AND scm.`CategoryID`='LSHHI3'    INNER JOIN investigation_observationtype iot ON iot.Investigation_ID=im.Investigation_Id    INNER JOIN observationtype_master otm ON otm.ObservationType_ID=iot.ObservationType_Id     inner JOIN centre_master cma ON cma.CentreID = t.CentreId    INNER JOIN f_panel_master pnl ON pnl.`Panel_ID`=t.Panel_ID     ORDER BY LabNo,IFNULL(printOrder,9999),labPriorty )ttt  UNION ALL  SELECT t.*,   '' AS Interpretation, '3' ReportType,IF(IM.PrintSampleName=0,IF(IFNULL(itm.Inv_ShortName,'')!='',IM.Name,IM.Name),im.name) AS TestName,im.PrintTestCentre,im.method, im.FileLimitationName,scm.deptinterpretaion,scm.ReportLine1,scm.ReportLine2,(select Address from centre_master where CentreId=t.TestCentreID)TCentreAdd,  otm.Name AS Dept,otm.isVisible AS DeptVisible,  IM.Print_sequence AS Print_Sequence_Investigation , im.PrintSeparate,im.printsamplename,'1' ShowInReport,  IM.Description AS d1,'' Priorty,'' IsParent,im.printHeader     AS invHeader    , '' SampleTypeID,cma.Centre CentreName,cma.Address,cma.Landline CentreLandline,cma.Mobile CentreMobile,cma.Email CentreEmail,ifnull((select NablLogoPath from centre_master where centreID=t.TestCentreID),'')NablLogoPath,ifnull((select CapLogoPath from centre_master where centreID=t.TestCentreID),'')CapLogoPath,ifnull((select NabhLogoPath from centre_master where centreID=t.TestCentreID),'')NabhLogoPath,'' printOrder ,cma.type1,  IFNULL((SELECT isNABL FROM `investiagtion_isnabl` WHERE Investigation_ID=im.Investigation_Id AND centreid=t.TestCentreID),0)isNABL   ,otm.Print_Sequence Print_Sequence_Dept,999 printOrder_labObservation1,999 printOrder_labObservation2 , otm.ObservationType_ID, pnl.`Panel_Code`,  pnl.ReportHeaderHeight,pnl.ReportHeaderXPosition,pnl.ReportHeaderYPosition,pnl.ReportFooterHeight,pnl.ReportBackGroundImage,pnl.AAALogo,pnl.TagProcessingLabID,IFNULL(LEFT(pnl.Add1,30),'')ClientAddress   FROM (    SELECT    pli.ItemId,lt.Password_Web,  pli.LedgerTransactionNo AS LabNo,pli.test_id PLOID,pli.laboutsrcid,IF(lt.HomeVisitBoyID='N/A','N/A',(SELECT  flm.NAME FROM feildboy_master flm WHERE flm.FeildBoyID=lt.HomeVisitBoyID)) FieldExe,pli.InterpretationID,pli.InterpretationType,   replace(Replace(lt.PName,'Master','Master '),'.','. ')PName,lt.age Age,pli.BarcodeNo,left(lt.Gender,1)Gender,IF(pli.Approved=0,'Provisional Report','Final Report')ReportStatus ,lt.OtherReferLab,lt.Interface_companyName,lt.CorporateIDCard,lt.PatientIDProofNo,lt.PatientIDProof,lt.LedgerTransactionNo_Interface,  DATE_FORMAT(pli.date,'%d/%b/%Y %I:%i%p') DateEnrolled,lt.PassPortNo,lt.PureHealthID,lt.Nationality,lt.SRFNo,lt.Patient_ID Patient_ID,'' Description,pli.SampleBySelf,'' LabObservation_ID,pli.Test_ID,  DATE_FORMAT(pli.ResultEnteredDate,'%d-%b-%y')AS ResultDate,pli.ResultEnteredBy ,if(pli.ForwardBy=pli.ApprovedBy,'',pli.ForwardBy) ForwardBy ,        (SELECT LabInves_Description FROM patient_labobservation_opd_text WHERE PLO_ID = pli.test_id) VALUE,'' `MinValue`,'' `MaxValue`, '' Flag,'' abnormalimage,pli.sampletypename samplename,'' LabObservationName,pli.Investigation_ID,pli.SlideNumber,pli.TestCentreID,      DATE_FORMAT(pli.SampleCollectionDate,'%d/%b/%Y %I:%i%p')SampleDate,  DATE_FORMAT(pli.SampleReceiveDate,'%d/%b/%Y %I:%i%p')ReceiveDate, DATE_FORMAT(pli.Date,'%d-%b-%y %h:%i %p')DATE,  IF(pli.Approved=1,1,0)Approved,(SELECT comments FROM patient_labinvestigation_opd_comments ploc WHERE ploc.Test_ID = pli.Test_ID)comments,        IF(pli.isHold=1,'Hold',  IF(pli.Approved=1,'Approved',IF(pli.isForward=1,'Forward',''))) AS MYApproved,  IF(pli.isHold=1,pli.HoldByName,IF(pli.Approved=1,'',IF(pli.isForward=1,pli.ForwardByName,'')))   AS MYApprovedBy,       DATE_FORMAT(pli.ApprovedDate,'%d/%b/%Y %I:%i%p')ApprovedDate,    pli.ApprovedBy,        DATE_FORMAT(pli.ResultEnteredDate,'%d/%b/%Y %I:%i%p')ResultEnteredDate,    pli.ResultEnteredName,        '' AS Unit,'' Child_Flag,    ''  labPriorty,'' DisplayReading,if(pli.IsPackage=1,pli.PackageName,'') PackageName, pli.IsPackage,   '' DispatchMode    ,'' ObsInterpretation, '' MethodName,DATE_FORMAT(pli.SamplecollectionDate,'%d/%b/%Y %I:%i%p')SegratedDate,DATE_FORMAT(NOW(),'%d/%b/%Y %I:%i%p')PrintDate,'0' IsCommentField ,0 IsOrganism,'' OrganismID,'' AbnormalValue,0 IsBold,0 IsUnderLine,0 `IsMic`,0 SepratePrint ,0 ShowDeltaReport,0 isborder,'1' MachineID    , lt.centreid,lt.Panel_ID,Left(Concat('Dr.',UPPER(lt.`DoctorName`)),30) ReferDoctor, IF(lt.OtherReferLab='',left(lt.panelname,25),lt.OtherReferLab) Company_Name ,'' Company_code,'' Company_add,lt.`HLMPatientType`,lt.`HLMOPDIPDNo`,pli.IsSampleCollectedByPatient,IFNULL(lt.PatientType,'')PatientType FROM patient_labinvestigation_opd pli     INNER JOIN f_ledgertransaction lt ON lt.LedgerTransactionID=pli.LedgerTransactionID   AND pli.Test_ID IN ({0})  AND pli.Result_Flag = 1  AND pli.ReportType in(3) AND pli.ishold=0     )t    INNER JOIN investigation_master IM ON IM.Investigation_Id = t.Investigation_ID and t.value<>''  INNER JOIN f_itemmaster itm ON itm.Type_ID = IM.Investigation_ID     INNER JOIN `f_subcategorymaster` scm ON scm.`SubCategoryID`=itm.`SubCategoryID` AND scm.`CategoryID`='LSHHI3'     INNER JOIN investigation_observationtype iot ON iot.Investigation_ID=im.Investigation_Id    INNER JOIN observationtype_master otm ON otm.ObservationType_ID=iot.ObservationType_Id      inner JOIN centre_master cma ON cma.CentreID = t.CentreId    INNER JOIN f_panel_master pnl ON pnl.`Panel_ID`=t.Panel_ID     ORDER BY Company_Name,LabNo,approved ,Print_Sequence_Dept,Dept,Print_Sequence_Investigation ,printOrder_labObservation1,printOrder_labObservation2 ;