1

I have this query below and it gives me the result attached as result.jpg, but I want to generate the report on a monthly basis.

I have attached the expected result as monthly.jpg

ResultMonthly

<td>
    <table class="table " id="List">
        <!--<thead>
            <tr>
                <th class='left'>
                    Body system
                </th>
                <th class='left'>
                    Number of cases
                </th>
            </tr>
        </thead>-->
        <tbody>
            <?php 
            // Get a db connection.
            $db = JFactory::getDbo();

            // Create a new query object.
            $query = $db->getQuery(true);

            // Select all records from the user profile table where key begins with "custom.".
            // Order it by the ordering field.
            $query->select($db->quoteName('condition'));
            $query->from($db->quoteName('#__consultations'));
            $query->where('(state = 1)');
            if ($date_from) 
                $query->where($db->quoteName('date_issue') . ' >= "'.$date_from.'"');
            if ($date_to) 
                $query->where($db->quoteName('date_issue') . ' <= "'.$date_to.'"');

            // Reset the query using our newly populated query object.
            $db->setQuery($query);

            // Load the results as a list of stdClass objects (see later for more options on retrieving data).
            $results = $db->loadColumn();
            $incident_level = "";
            foreach ($results as $condition) {
                if ($condition) {
                    $injuries = json_decode($condition);
                    $incident_level.= implode(",",$injuries).",";
                }
                //var_dump($injuries);
                //echo "<br/>";
                //$incident_level = array_merge($incident_level, $injuries);
            }
            //$incident_level = explode (",",$incident_level);
            $incident_level = array_map('intval', explode(',',$incident_level));
            //var_dump($incident_level);
            $counts = array_count_values($incident_level);
            ?>
            <?php 
            // Get a db connection.
            $db = JFactory::getDbo();

            // Create a new query object.
            $query = $db->getQuery(true);

            // Select all records from the user profile table where key begins with "custom.".
            // Order it by the ordering field.
            $query->select('*');
            $query->from($db->quoteName('#__conditions'));
            $query->where($db->quoteName('state') . ' = 1');
            $query->order('ordering ASC');

            // Reset the query using our newly populated query object.
            $db->setQuery($query);

            // Load the results as a list of stdClass objects (see later for more options on retrieving data).
            $health_condition = $db->loadObjectList();
            foreach ($health_condition as $i => $item) :
                ?>
                <tr class="<?php if ($counts[$item->id] < 1) echo 'hidden'?>">
                    <td>
                        <?php //echo $this->escape($item->name); ?>
                    </td>   
                    <td>
                        <?php //echo $counts[$item->id]; ?>
                    </td>               
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</td>
<td>
    <script type="text/javascript">
        google.charts.load("current", {packages:["corechart"]});
        google.charts.setOnLoadCallback(drawChart);
        function drawChart() {
            var data = google.visualization.arrayToDataTable([
                           ['Task', 'Hours per Month'],
                           <?php
                           foreach ($condition as $i => $item) {
                               if ($counts[$item->id] > 0) {
                                   ?>
                                   ['<?php echo $item->name; ?>',
                                     <?php  echo $counts[$item->id]; ?>],   
                                   <?php
                               }
                           }
                           ?>
            ]);

            var options = {title: 'Health conditions'};
            var chart = new google.visualization.ColumnChart(document.getElementById('columnchart_values'));
            chart.draw(data, options);
        }
    </script>
    <div id="columnchart_values" style="width: 600px; height: 400px;"></div>
</td>
</tr>

<td>
    <?php 
        echo $counts[$item->id];
    ?>
</td>               
</tr>
  • It will be great you explain further and elaborate on what you are trying to achieve with your code for clarity; as it stands, it's not really clear. – nyedidikeke Feb 28 '17 at 16:33
  • You haven't given us much to work with here. I must say, $injuries = json_decode($condition); is very concerning -- looks like your database design needs to be Normalized. If you would still like support for this question, please supply us with an SQLFiddle demo that holds the relevant data. – mickmackusa Jun 8 '18 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.