SELECT 
    *
FROM
    (SELECT 
       v.BenVisitID,
            v.BeneficiaryRegID,
            idi.BeneficiaryID,
            idi.FirstName,
            idi.LastName,
            idi.fathername,
            idi.spousename,
                       v.ProviderServiceMapID,
            
            
            v.VisitCode,
            v.VisitDateTime,
            idi.registrationDate,
            v.VisitNo,
            CASE
                WHEN v.VisitNo = 1 THEN 'Reg'
                ELSE 'Rev'
            END AS VisitType,
            v.VisitReason,
            v.VisitCategory,
            v.createdby,
            v.CreatedDate,
            DATE_FORMAT(v.CreatedDate, '%b-%y') AS CreatedDate2,
            DATE_FORMAT(v.CreatedDate, '%c') AS MM,
            DATE_FORMAT(v.CreatedDate, '%d') AS DD,
            DATE_FORMAT(v.CreatedDate, '%Y') AS YYYY,
            'Month' AS Period,
            DATE_FORMAT(v.CreatedDate, '%H:%i') AS Time,
            v.VanID,
            v.ParkingPlaceID,
            round(datediff(v.createddate,idi.dob)/365,0) As Age,
                     CASE
                WHEN round(datediff(v.createddate,idi.dob)/365,0) < 6 THEN '<6'
                WHEN round(datediff(v.createddate,idi.dob)/365,0) < 11 THEN '6-10'
                WHEN round(datediff(v.createddate,idi.dob)/365,0) < 20 THEN '11-19'
                WHEN round(datediff(v.createddate,idi.dob)/365,0) < 30 THEN '20-29'
                WHEN round(datediff(v.createddate,idi.dob)/365,0) < 40 THEN '30-40'
                WHEN round(datediff(v.createddate,idi.dob)/365,0) < 50 THEN '40-50'
                WHEN round(datediff(v.createddate,idi.dob)/365,0) < 60 THEN '50-60'
                ELSE '>60'
            END AS AgeGroup,
            idi.gender,
            idi.CurrDistrict,
            idi.CurrVillage,
            idi.CurrServicePoint,
            an.Weight_Kg,
            an.Height_cm,
            an.BMI,
            CASE
                WHEN an.BMI < 18.5 THEN 'Under Weight'
                WHEN an.BMI < 25 THEN 'Normal'
                WHEN an.BMI < 30 THEN 'Over Weight'
                ELSE 'Obese'
            END AS BMI_Result,
            p.rbs As Nurse_RBS_Test,
            v.PregnancyStatus,
            v.RCHID,
            p.BloodGlucose_Random,
            p.SystolicBP_1stReading,
            p.DiastolicBP_1stReading,
            CASE
                WHEN
                    p.SystolicBP_1stReading IS NULL
                        AND p.DiastolicBP_1stReading IS NULL
                THEN
                    ''
                WHEN
                    p.SystolicBP_1stReading < 90
                        AND p.DiastolicBP_1stReading < 60
                THEN
                    'Low'
                WHEN
                    p.SystolicBP_1stReading < 121
                        AND p.DiastolicBP_1stReading < 81
                THEN
                    'Normal'
                WHEN
                    p.SystolicBP_1stReading < 141
                        AND p.DiastolicBP_1stReading < 91
                THEN
                    'Pre Hypertension'
                ELSE 'Hypertension'
            END AS Hypertension_status,
            p.Temperature,
            p.PulseRate,
            p.RespiratoryRate,
            bp.TobaccoUseStatus,
            bp.AlcoholIntakeStatus,
            com.ComorbidCondition,
            com.OtherComorbidCondition,
            chief.ChiefComplaint,
            c.ClinicalObservation,
            c.OtherSymptoms,
            SUBSTRING_INDEX(SUBSTRING_INDEX(pre.DiagnosisProvided, '||', 1), '||', - 1) AS DiagnosisProvided1,
            CASE
                WHEN STRCMP(SUBSTRING_INDEX(SUBSTRING_INDEX(pre.DiagnosisProvided, '||', 1), '||', - 1), SUBSTRING_INDEX(SUBSTRING_INDEX(pre.DiagnosisProvided, '||', 2), '||', - 1)) <> 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(pre.DiagnosisProvided, '||', 2), '||', - 1)
            END AS DiagnosisProvided2,
            CASE
                WHEN STRCMP(SUBSTRING_INDEX(SUBSTRING_INDEX(pre.DiagnosisProvided, '||', 2), '||', - 1), SUBSTRING_INDEX(SUBSTRING_INDEX(pre.DiagnosisProvided, '||', 3), '||', - 1)) <> 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(pre.DiagnosisProvided, '||', 3), '||', - 1)
            END AS DiagnosisProvided3,
            CASE
                WHEN STRCMP(SUBSTRING_INDEX(SUBSTRING_INDEX(pre.DiagnosisProvided, '||', 3), '||', - 1), SUBSTRING_INDEX(SUBSTRING_INDEX(pre.DiagnosisProvided, '||', 4), '||', - 1)) <> 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(pre.DiagnosisProvided, '||', 4), '||', - 1)
            END AS DiagnosisProvided4,
            CASE
                WHEN STRCMP(SUBSTRING_INDEX(SUBSTRING_INDEX(pre.DiagnosisProvided, '||', 4), '||', - 1), SUBSTRING_INDEX(SUBSTRING_INDEX(pre.DiagnosisProvided, '||', 5), '||', - 1)) <> 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(pre.DiagnosisProvided, '||', 5), '||', - 1)
            END AS DiagnosisProvided5,
            nd.NCD_condition,
            nd.NCD_Complication,
            nd.NCDCareType,
            ref.referredToInstituteID,
            ref.referredToInstituteName,
            idi.maritalstatus,
            idi.literacystatus,
            idi.education,
            idi.occupation,
            idi.incomestatus,
            idi.community,
            idi.religion
    FROM
        db_iemr.t_benvisitdetail v
      LEFT JOIN db_iemr.t_phy_vitals p ON  p.visitcode = v.visitcode
    LEFT JOIN db_iemr.t_phy_anthropometry an ON  an.visitcode= v.visitcode
    LEFT JOIN (SELECT 
        mp.BenRegId,bm.BeneficiaryID,
            mp.createddate AS registrationdate,
            d.FirstName,
            d.LastName,
            d.FatherName,
            d.SpouseName,
           -- d.DOB,
            d.gender,
            d.maritalstatus,
            d.literacystatus,
            (case when d.DOB is not null then d.dob else JSON_UNQUOTE(JSON_EXTRACT(ExtraFields, '$.dob')) end ) AS dob,
 (case when d.incomeStatus is not null then d.incomeStatus else JSON_UNQUOTE(JSON_EXTRACT(ExtraFields, '$.incomeName')) end) AS incomestatus,
(case when d.occupation is not null then d.occupation else
  JSON_UNQUOTE(JSON_EXTRACT(d.ExtraFields, '$.occupationOther')) end) AS occupation,
  (case when d.education is not null then d.education else JSON_UNQUOTE(JSON_EXTRACT(d.ExtraFields, '$.educationQualificationName')) end) AS education,
( case when d.religion is not null then d.religion else JSON_UNQUOTE(JSON_EXTRACT(d.ExtraFields, '$.Religion')) End) AS religion,

        --    d.education,
          --  d.occupation,
           -- d.incomestatus,
            d.community,
           -- d.religion,
            ad.CurrDistrict,
            ad.CurrVillage,
            ad.CurrServicePoint
    FROM db_identity.i_beneficiarymapping mp
        left join db_identity.m_beneficiaryregidmapping bm on bm.benregid=mp.benregid
    left JOIN db_identity.i_beneficiarydetails d ON d.VanSerialNo=mp.bendetailsid and d.VanID=mp.vanid
           LEFT JOIN db_identity.i_beneficiaryaddress ad ON ad.VanSerialNo=mp.BenAddressId and ad.VanID=mp.VanID
           -- LEFT JOIN db_identity.i_beneficiarycontacts cd1 ON mp.BenContactsId = cd1.BenContactsID
        ) idi ON idi.BenRegId = v.BeneficiaryRegID
    LEFT JOIN (SELECT 
                   visitcode,
            GROUP_CONCAT(DISTINCT ClinicalObservation) AS ClinicalObservation,
            GROUP_CONCAT(DISTINCT OtherSymptoms) AS OtherSymptoms
    FROM
        db_iemr.t_benclinicalobservation
    GROUP BY 1) c ON  c.visitcode = v.visitcode
    LEFT JOIN (SELECT 
                   visitcode,
            GROUP_CONCAT(DISTINCT ChiefComplaint) AS ChiefComplaint
    FROM
        db_iemr.t_benchiefcomplaint
    GROUP BY 1) chief ON chief.visitcode=v.visitcode
    LEFT JOIN (SELECT 
	           visitcode,
            GROUP_CONCAT(DISTINCT ComorbidCondition) AS ComorbidCondition,
            GROUP_CONCAT(DISTINCT OtherComorbidCondition) AS OtherComorbidCondition
    FROM
        db_iemr.t_bencomorbiditycondition
    GROUP BY 1 ) com ON com.visitcode = v.VisitCode
    LEFT JOIN (SELECT 
                    Visitcode,
            GROUP_CONCAT(DISTINCT IF(DiagnosisProvided = '', NULL, DiagnosisProvided)
                SEPARATOR '||') AS DiagnosisProvided
    FROM
        db_iemr.t_prescription
    GROUP BY 1 ) pre on  pre.visitcode = v.visitcode
    LEFT JOIN (SELECT 
                   visitcode,
            GROUP_CONCAT(DISTINCT TobaccoUseStatus) AS TobaccoUseStatus,
            GROUP_CONCAT(DISTINCT AlcoholIntakeStatus) AS AlcoholIntakeStatus
    FROM
        db_iemr.t_benpersonalhabit where deleted=0
    GROUP BY 1) bp ON bp.visitcode = v.visitcode
    LEFT JOIN (SELECT 
        visitcode,
            GROUP_CONCAT(DISTINCT referredToInstituteID) referredToInstituteID,
            GROUP_CONCAT(DISTINCT referredToInstituteName) referredToInstituteName
    FROM
        db_iemr.t_benreferdetails
    GROUP BY 1) ref ON ref.visitcode = v.visitcode
    LEFT JOIN (SELECT 
        visitcode,
            GROUP_CONCAT(DISTINCT NCD_condition) NCD_condition,
            GROUP_CONCAT(DISTINCT NCD_Complication) NCD_Complication,
            GROUP_CONCAT(DISTINCT NCDCareType) NCDCareType
    FROM
        db_iemr.t_ncddiagnosis
    GROUP BY 1) nd ON nd.visitcode = v.visitcode
    WHERE  -- v.ProviderServiceMapID=10  and
        v.CreatedDate BETWEEN '2025-08-11 00:00:00' and '2025-08-31 23:59:59'
        -- DATE_FORMAT(SUBDATE(NOW(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
        ) vitals
        LEFT JOIN
    (SELECT 
       visitcode,
            (RBS IS NOT NULL) + (HbA1c IS NOT NULL) + (Haemoglobin IS NOT NULL) + (UrineAlbumin IS NOT NULL) + (UrineSugar IS NOT NULL) + (UrinePregnancyTest IS NOT NULL) + (MalariaRDT IS NOT NULL) + (LiverFunctionTest IS NOT NULL) + (RenalFunctionTest IS NOT NULL) + (LipidProfile IS NOT NULL) + (CompleteBloodPicture IS NOT NULL) + (ESR IS NOT NULL) + (FastingBloodSugar IS NOT NULL) + (PostPrandialBloodSugar IS NOT NULL) AS Total_lab_test,
            RBS,
            HbA1c,
            Haemoglobin,
            UrineAlbumin,
            UrineSugar,
            UrinePregnancyTest,
            MalariaRDT,
            LiverFunctionTest,
            RenalFunctionTest,
            LipidProfile,
            CompleteBloodPicture,
            ESR,
            FastingBloodSugar,
            PostPrandialBloodSugar,
            CBP_Hemoglobin,
            CASE
                WHEN RBS < 140 THEN '1'
                WHEN RBS < 200 THEN '2'
                ELSE '3'
            END AS RBS_Status
    FROM
        (SELECT 
        t.visitcode,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='Random Blood Glucose (RBS)' THEN tr.TestResultValue
            END) RBS,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='HbA1c' THEN tr.TestResultValue
            END) HbA1c,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='Hemoglobin (Hb)' THEN tr.TestResultValue
            END) Haemoglobin,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='Urine Albumin' THEN tr.TestResultValue
            END) UrineAlbumin,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='Urine Sugar' THEN tr.TestResultValue
            END) UrineSugar,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='Urine Pregnancy Test (UPT)' THEN tr.TestResultValue
            END) UrinePregnancyTest,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='RDT Malaria Test' THEN tr.TestResultValue
            END) MalariaRDT,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='Liver Function Test (LFT)' THEN tr.TestResultValue
            END) LiverFunctionTest,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='Renal Function Test (RFT)' THEN tr.TestResultValue
            END) RenalFunctionTest,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='Lipid Profile' THEN tr.TestResultValue
            END) LipidProfile,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='Complete Blood Picture (CBP)' THEN tr.TestResultValue
            END) CompleteBloodPicture,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='Erythrocyte Sedimentation Rate (ESR)' THEN tr.TestResultValue
            END) ESR,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='Fasting Blood Sugar (FBS)' THEN tr.TestResultValue
            END) FastingBloodSugar,
            GROUP_CONCAT(CASE
                WHEN t.ProcedureName='Post Lunch Blood Sugar (PLBS)' THEN tr.TestResultValue
            END) PostPrandialBloodSugar,
           GROUP_CONCAT(distinct CASE
                WHEN pp.ProcedureName='Complete Blood Picture (CBP)' and mt.TestComponentName='Hemoglobin (Hb)' THEN tr.TestResultValue
            END) As CBP_Hemoglobin
    FROM
        db_iemr.t_lab_testorder t
        inner join db_iemr.m_procedure pp  on pp.procedureid=t.procedureid
    LEFT JOIN db_iemr.t_lab_testresult tr ON t.visitcode = tr.VisitCode
        AND t.ProcedureID = tr.ProcedureID 
        left join db_iemr.m_testcomponent mt on mt.TestComponentID=tr.TestComponentID
        where pp.deleted=0
    GROUP BY 1) AS temp) l ON vitals.visitcode = l.visitcode
        LEFT JOIN
    (SELECT 
        a.visitcode,
            cnt AS Total_drug,
            CASE
                WHEN cnt > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.GenericDrugName, ',', cnt), ',', - 1)
            END AS drug_1,
            CASE
                WHEN cnt > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.QtyPrescribed, ',', cnt), ',', - 1)
            END AS drug1_Qty,
            CASE
                WHEN cnt - 1 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.GenericDrugName, ',', cnt - 1), ',', - 1)
            END AS drug_2,
            CASE
                WHEN cnt - 1 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.QtyPrescribed, ',', cnt - 1), ',', - 1)
            END AS drug2_Qty,
            CASE
                WHEN cnt - 2 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.GenericDrugName, ',', cnt - 2), ',', - 1)
            END AS drug_3,
            CASE
                WHEN cnt - 2 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.QtyPrescribed, ',', cnt - 2), ',', - 1)
            END AS drug3_Qty,
            CASE
                WHEN cnt - 3 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.GenericDrugName, ',', cnt - 3), ',', - 1)
            END AS drug_4,
            CASE
                WHEN cnt - 3 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.QtyPrescribed, ',', cnt - 3), ',', - 1)
            END AS drug4_Qty,
            CASE
                WHEN cnt - 4 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.GenericDrugName, ',', cnt - 4), ',', - 1)
            END AS drug_5,
            CASE
                WHEN cnt - 4 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.QtyPrescribed, ',', cnt - 4), ',', - 1)
            END AS drug5_Qty,
            CASE
                WHEN cnt - 5 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.GenericDrugName, ',', cnt - 5), ',', - 1)
            END AS drug_6,
            CASE
                WHEN cnt - 5 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.QtyPrescribed, ',', cnt - 5), ',', - 1)
            END AS drug6_Qty,
            CASE
                WHEN cnt - 6 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.GenericDrugName, ',', cnt - 6), ',', - 1)
            END AS drug_7,
            CASE
                WHEN cnt - 6 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.QtyPrescribed, ',', cnt - 6), ',', - 1)
            END AS drug7_Qty,
            CASE
                WHEN cnt - 7 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.GenericDrugName, ',', cnt - 7), ',', - 1)
            END AS drug_8,
            CASE
                WHEN cnt - 7 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.QtyPrescribed, ',', cnt - 7), ',', - 1)
            END AS drug8_Qty,
            CASE
                WHEN cnt - 8 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.GenericDrugName, ',', cnt - 8), ',', - 1)
            END AS drug_9,
            CASE
                WHEN cnt - 8 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.QtyPrescribed, ',', cnt - 8), ',', - 1)
            END AS drug9_Qty,
            CASE
                WHEN cnt - 9 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.GenericDrugName, ',', cnt - 9), ',', - 1)
            END AS drug_10,
            CASE
                WHEN cnt - 9 > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(a.QtyPrescribed, ',', cnt - 9), ',', - 1)
            END AS drug10_Qty
    FROM
        (SELECT 
        visitcode,
            GROUP_CONCAT(GenericDrugName) GenericDrugName,
            GROUP_CONCAT(QtyPrescribed) QtyPrescribed,
            COUNT(*) AS cnt
    FROM
        db_iemr.t_prescribeddrug where ProviderServiceMapID=10
    GROUP BY 1) a) drug ON vitals.visitcode = drug.visitcode
        LEFT JOIN
    (SELECT
        pxx.VisitCode, GROUP_CONCAT(ixx.Quantity) AS dispensed_qty
    FROM
        db_iemr.t_patientissue AS pxx
    LEFT JOIN
         db_iemr.t_benvisitdetail v ON PXX.VISITCODE = V.VISITCODE
    LEFT JOIN db_iemr.t_itemstockexit AS ixx ON pxx.VanSerialNo = ixx.ExitTypeID and pxx.VanID = ixx.VanID
    -- WHERE v.ProviderServiceMapID=10 -- and v.CreatedDate  BETWEEN DATE_FORMAT(SUBDATE(NOW(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
    GROUP BY pxx.VisitCode) disp ON vitals.visitcode = disp.visitcode;