1test.php

<!-- <div class="row" id="SetGroupData">
                                        <div class="col-lg-12 col-md-12 col-12">
                                            <div class="medicine-group-details-box">
                                                <div class="row">
                                                    <div class="col-md-12 col-lg-4 form-group p-l-0 p-r-5">
                                                       <label for="dropdown">Medicine Name </label>
                                                        <select class="form-control required-field" id="medicineId" name="medicineId">
                                                            <?php 
                                                                if($_GET['id'] == '' || $row['medicineId'] == '0') {
                                                                ?>
                                                            <option value="">--Select--</option>
                                                            <?php 
                                                                } else {
                                                                    $querySel = mysqli_query($con,"SELECT * FROM `tbl_medicine` WHERE `medicineId` = '".$row['medicineId']."'");
                                                                    $rowSel = mysqli_fetch_array($querySel); 
                                                                ?>
                                                            <option value="<?php echo $rowSel['medicineId']; ?>">
                                                                <?php echo $rowSel['medicineName']; ?>
                                                            </option>
                                                            <?php 
                                                                }
                                                                $query1 = mysqli_query($con,"SELECT * FROM `tbl_medicine` WHERE `medicineId` != '".$row['medicineId']."' AND `deleteStatus` = '0'");
                                                                while($row1 = mysqli_fetch_array($query1)) {
                                                                ?>
                                                            <option value="<?php echo $row1['medicineId']; ?>">
                                                                <?php echo $row1['medicineName']; ?>
                                                            </option>
                                                            <?php
                                                                }
                                                                ?>
                                                        </select>
                                                    </div>
                                                    <div class="col-md-12 col-lg-2 form-group p-l-0 p-r-5">
                                                       <label for="dropdown">Dose </label>
                                                        <select class="form-control required-field" id="dosageId" name="dosageId">
                                                            <?php 
                                                                if($_GET['id'] == '' || $row['dosageId'] == '0') {
                                                                ?>
                                                            <option value="">--Select--</option>
                                                            <?php 
                                                                } else {
                                                                    $querySel = mysqli_query($con,"SELECT * FROM `tbl_dosage` WHERE `dosageId` = '".$row['dosageId']."'");
                                                                    $rowSel = mysqli_fetch_array($querySel); 
                                                                ?>
                                                            <option value="<?php echo $rowSel['dosageId']; ?>">
                                                                <?php echo $rowSel['name']; ?>
                                                            </option>
                                                            <?php 
                                                                }
                                                                $query1 = mysqli_query($con,"SELECT * FROM `tbl_dosage` WHERE `dosageId` != '".$row['dosageId']."' AND `deleteStatus` = '0'");
                                                                while($row1 = mysqli_fetch_array($query1)) {
                                                                ?>
                                                            <option value="<?php echo $row1['dosageId']; ?>">
                                                                <?php echo $row1['name']; ?>
                                                            </option>
                                                            <?php
                                                                }
                                                                ?>
                                                        </select>
                                                    </div>

<div class="col-md-12 col-lg-3 form-group p-l-0 p-r-5">
                                                       <label for="dropdown">Instruction  </label>
                                                        <select class="form-control required-field" id="miId" name="miId">
                                                            <?php 
                                                                if($_GET['id'] == '' || $row['miId'] == '0') {
                                                                ?>
                                                            <option value="">--Select--</option>
                                                            <?php 
                                                                } else {
                                                                    $querySel = mysqli_query($con,"SELECT * FROM `tbl_medicine_instruction` WHERE `miId` = '".$row['miId']."'");
                                                                    $rowSel = mysqli_fetch_array($querySel); 
                                                                ?>
                                                            <option value="<?php echo $rowSel['miId']; ?>">
                                                                <?php echo $rowSel['name']; ?>
                                                            </option>
                                                            <?php 
                                                                }
                                                                $query1 = mysqli_query($con,"SELECT * FROM `tbl_medicine_instruction` WHERE `miId` != '".$row['miId']."' AND `deleteStatus` = '0'");
                                                                while($row1 = mysqli_fetch_array($query1)) {
                                                                ?>
                                                            <option value="<?php echo $row1['miId']; ?>">
                                                                <?php echo $row1['name']; ?>
                                                            </option>
                                                            <?php
                                                                }
                                                                ?>
                                                        </select>
                                                    </div>

// $sql = "
                                            //         SET @sql = NULL;

//         SELECT
                                            //           GROUP_CONCAT(
                                            //             DISTINCT
                                            //             CONCAT(
                                            //               'SUM(CASE WHEN  c.caseType = ''',
                                            //               caseType,
                                            //               ''' AND c.deleteStatus = 0 THEN 1 ELSE 0 END) AS ',
                                            //               REPLACE(caseType, ' ', '_'),
                                            //               ', ',
                                            //               'SUM(CASE WHEN c.caseType = ''',
                                            //               caseType,
                                            //               ''' AND c.deleteStatus = 0 THEN c.caseFee ELSE 0 END) AS ',
                                            //               REPLACE(caseType, ' ', '_'),
                                            //               '_Revenue'
                                            //             )
                                            //           ) INTO @sql
                                            //         FROM tbl_case;

//         SET @sql = CONCAT('
                                            //           SELECT
                                            //             r.receptionName,
                                            //             p.receptionId, ', @sql, ',
                                            //             COUNT(*) AS Total,
                                            //             SUM(CASE WHEN c.deleteStatus = 0 THEN c.caseFee ELSE 0 END) AS Total_Revenue
                                            //           FROM tbl_patient p
                                            //           JOIN tbl_case c ON p.caseId = c.caseId
                                            //           JOIN tbl_reception r ON p.receptionId = r.receptionId
                                            //           WHERE c.deleteStatus = 0
                                            //           GROUP BY r.receptionName, p.receptionId
                                            //           ORDER BY p.receptionId;
                                            //         ');
                                                    
                                            //         PREPARE stmt FROM @sql;
                                            //         EXECUTE stmt;
                                            //         DEALLOCATE PREPARE stmt;
                                            //     ";

<div class="row">
                                        <?php
                                           
                                            mysqli_query($con, "SET SESSION group_concat_max_len = 1000000");

$sql = "
                                                    SET @sql = NULL;

SELECT
                                                      GROUP_CONCAT(
                                                        DISTINCT
                                                        CONCAT(
                                                          'SUM(CASE WHEN c.caseType = ', QUOTE(caseType), ' AND c.deleteStatus = 0 THEN 1 ELSE 0 END) AS `', 
                                                          REPLACE(caseType, ' ', '_'), '`, ',
                                                          'SUM(CASE WHEN c.caseType = ', QUOTE(caseType), ' AND c.deleteStatus = 0 THEN c.caseFee ELSE 0 END) AS `', 
                                                          REPLACE(caseType, ' ', '_'), '_Revenue`'
                                                        )
                                                      ) INTO @sql
                                                    FROM tbl_case WHERE deleteStatus = '0';

SET @full_query = CONCAT('
                                                      SELECT
                                                        r.receptionName,
                                                        p.receptionId, ', @sql, ',
                                                        COUNT(*) AS Total,
                                                        SUM(CASE WHEN c.deleteStatus = 0 THEN c.caseFee ELSE 0 END) AS Total_Revenue
                                                      FROM tbl_patient p
                                                      JOIN tbl_case c ON p.caseId = c.caseId
                                                      JOIN tbl_reception r ON p.receptionId = r.receptionId
                                                      WHERE c.deleteStatus = 0
                                                      GROUP BY r.receptionName, p.receptionId
                                                      ORDER BY p.receptionId;
                                                    ');

SELECT @full_query;

PREPARE stmt FROM @full_query;
                                                    EXECUTE stmt;
                                                    DEALLOCATE PREPARE stmt;";
                                                // echo $sql;
                                                // exit;
                                            if (mysqli_multi_query($con, $sql)) {
                                                do {
                                                    if ($result = mysqli_store_result($con)) {
                                                        if (mysqli_num_rows($result) > 0) {
                                                            $results = [];
                                                            
                                                            while ($row = mysqli_fetch_assoc($result)) {
                                                                $results[] = $row;
                                                            }
                                                            
                                                            foreach ($results as $row) {
                                                                $receptionName = htmlspecialchars($row['receptionName']);
                                                                $totalCases = htmlspecialchars($row['Total_Cases']);
                                                                $totalRevenue = htmlspecialchars($row['Total_Revenue']);
                                                                
                                                                echo '<div class="col-md-3 col-6 p-l-5 p-r-5">';
                                                                echo '    <a href="#">';
                                                                echo '        <div class="homeCard paymentBg mb-3">';
                                                                echo '            <div class="card-body cardPadd">';
                                                                echo '                <div class="row">';
                                                                echo '                    <div class="col-md-4 p-l-5 p-r-5">';
                                                                echo '                        <div class="icon text-info mr-5">';
                                                                echo '                            <img src="./assets/images/icon/reception.png" class="homeIcon">';
                                                                echo '                        </div>';
                                                                echo '                    </div>';
                                                                echo '                    <div class="col-md-8" style="align-content: center;">';
                                                                echo '                        <div class="content">';
                                                                echo '                            <div class="cardText" style="margin-bottom: 5px;">';
                                                                echo                                $receptionName;
                                                                echo '                            </div>';
                                                                echo '                            <h6 class="number">';
                                                                
                                                                foreach ($row as $key => $value) {
                                                                    if (strpos($key, '_Revenue') !== false) {
                                                                        $caseType = str_replace('_Revenue', '', $key);
                                                                        $caseCountColumn = str_replace('_Revenue', '', $key);
                                                                        $caseCount = htmlspecialchars($row[$caseCountColumn] ?? 0);
                                                                        echo htmlspecialchars($caseType) . ' (' . $caseCount . ') : ' . htmlspecialchars($value) . '<br>';
                                                                    }
                                                                }
                                                                
                                                                echo '                            </h6>';
                                                                echo '                        </div>';
                                                                echo '                    </div>';
                                                                echo '                </div>';
                                                                echo '            </div>';
                                                                echo '        </div>';
                                                                echo '    </a>';
                                                                echo '</div>';
                                                            }
                                                        }
                                                        mysqli_free_result($result);
                                                    }
                                                } 
                                                while (mysqli_next_result($con));
                                            } else {
                                                echo "Error executing query: " . mysqli_error($con);
                                            }

?>
                                    </div>

docroot: /home/u856302147/domains/creativegardendesign.in/public_html

curdir: /home/u856302147/domains/vparking.live/public_html/doctor/MyDesk-18-9

	.	4096
	..	4096
	CSV	4096
X	ac.php	10930
X	addpatient.php	71032
X	ajax-edit.php	9426
	assets	4096
X	camera.png	8216
X	caseType.php	12012
X	chiefComplaint.php	12297
X	city.php	14102
X	conj.php	10948
X	cornea.php	10966
X	covertest.php	10993
X	db.php	386
X	demo.php	7031
X	diagnosis.php	12264
X	disc.php	10948
X	district.php	14113
X	doctor.php	19479
X	dosage.php	10962
X	duration.php	10942
X	em.php	10930
X	foc.php	19623
X	footer.php	14383
X	fr.php	10930
X	index.php	67221
X	iris.php	10948
X	kco.php	10921
X	lens.php	10948
X	lid.php	10939
X	location.php	12032
X	login.php	6430
X	logout.php	679
X	master_Dist.php	47475
X	master_NCT_info.php	10936
X	master_SAC_info.php	23989
X	master_TN_Wt_info.php	23890
X	master_TN_info.php	23883
X	master_Vn_gi_info.php	10961
X	master_Vn_gl_info.php	10954
X	master_Vn_info.php	10935
X	master_Vn_st_info.php	10954
X	master_axis.php	10923
X	master_eys_input_info.php	25324
X	master_near.php	47475
X	master_nr_GI_info.php	10962
X	master_nr_Vn_info.php	10948
X	master_pn_Vn_info.php	10958
X	master_sph_cyl_info.php	11880
X	medicine.php	22328
X	medicineInstruction.php	11083
X	medicineType.php	11022
X	medicinecategory.php	11044
X	medicinegroup.php	43187
X	meta-fevicon.php	688
X	online.php	52036
X	onlinetable.php	5051
X	onlineview.php	0
X	pagination.php	900
X	paginationDemo.php	892
X	paginationNew.php	892
X	paginationNew2.php	910
X	patient-list.php	19334
X	patienthistory.php	17432
X	patienttable.php	8680
	plugins	4096
X	primary-patient.php	295540
X	pupil.php	10957
X	reception.php	17734
X	referedBy.php	11010
X	report.php	15845
X	sac.php	10939
X	secondary-patient.php	314503
X	secondary-patient.zip	20049
X	setting.php	44844
X	show-entries.php	1445
X	show-entriesDemo.php	1451
X	show-entriesNew.php	1459
X	show-entriesNew2.php	1471
X	slot.php	10946
X	specialAdvice.php	13473
X	state.php	11263
X	switch_doctor.php	631
X	test.php	20763
X	test1.php	2253
X	top-menu.php	22349
	uploads	4096
X	viewmedicinegroup.php	8067