[Dashboard Widget] Month by month dashboard table

General Discussion
1

Can someone adapt this snippet?

 <div class="col-xs-12 col-md-6">

            <div id="panel-invoice-overview" class="panel panel-default overview">

                <div class="panel-heading">
                    <b><i class="fa fa-bar-chart fa-margin"></i>  <?php echo 'Monthly Comparison'; ?></b>
                    <span class="pull-right text-muted"> <?php echo 'Since 2023'; ?></span>
                </div>

              <?php
   $server = getenv('DB_HOSTNAME');
  $database = getenv('DB_DATABASE');
  $dbusername = getenv('DB_USERNAME');
  $dbpassword = getenv('DB_PASSWORD');

  try {
    $pdo = new PDO("mysql:host=$server;dbname=$database", $dbusername, $dbpassword);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //Improved error handling
  } catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
  }


  $months = array('01','02','03','04','05','06','07','08','09','10','11','12');
  $years = array('2023','2024','2025');

  echo '<div class="table-responsive"><table class="table table-bordered table-condensed no-margin">';

  foreach ($years as $year) {
    echo '<tr>';
    foreach ($months as $month) {
      $startDate = $year . '-' . $month . '-01';
      $endDate = $year . '-' . $month . '-31';

      try {
        $stmt = $pdo->prepare("SELECT invoice_status_id, 
                             SUM(CASE WHEN ip_invoices.invoice_status_id = 4 THEN ip_invoice_amounts.invoice_paid ELSE ip_invoice_amounts.invoice_balance END) AS sum_total, 
                                  COUNT(*) AS num_total
                                FROM ip_invoice_amounts
                                JOIN ip_invoices ON ip_invoices.invoice_id = ip_invoice_amounts.invoice_id
                                WHERE ip_invoices.invoice_date_created >= :startDate AND ip_invoices.invoice_date_created <= :endDate");

        $stmt->execute(['startDate' => $startDate, 'endDate' => $endDate]);
        $monthlyview = $stmt->fetchAll(PDO::FETCH_ASSOC);      
        if ($monthlyview) {
		  echo '<td style="font-weight: 300;">';
	        echo $month . '/' . $year . '<br />';
    	      foreach ($monthlyview as $result) {
        	    echo  number_format($result['sum_total']);
	          }	
		    echo '</td>';			  
        } else {
			echo '<td>';	     
    	      foreach ($monthlyview as $result) {
        	    echo '';
	          }	
		    echo '</td>';	
        }
   

      } catch (PDOException $e) {
        echo '<td>Error: ' . $e->getMessage() . '</td>';
      }
    }
    echo '</tr>';
  }

  echo '</table></div>';
  $pdo = null; //Close the connection
?>

            </div>
            </div>
2

What do you mean?
Do you want someone to help you to not use PDO inline in a vew .php file and just use the model instead?

    $pdo = new PDO("mysql:host=$server;dbname=$database", $dbusername, $dbpassword);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

^^^ just don’t do that.
InvoicePlane has files in application/modules/ that deal with this

$stmt = $pdo->prepare("SELECT invoice_status_id, 
                             SUM(CASE WHEN ip_invoices.invoice_status_id = 4 THEN ip_invoice_amounts.invoice_paid ELSE ip_invoice_amounts.invoice_balance END) AS sum_total, 
                                  COUNT(*) AS num_total
                                FROM ip_invoice_amounts
                                JOIN ip_invoices ON ip_invoices.invoice_id = ip_invoice_amounts.invoice_id
                                WHERE ip_invoices.invoice_date_created >= :startDate AND ip_invoices.invoice_date_created <= :endDate");

add a function to the model and call the function inside that model.

Don’t ever use
“select something from somewhere” inside a view file.
A view file is **only to show stuff to the world and to edit records, not to quickly select stuff from the database.

look in application/modules.
Find the module you want data from (invoices?)
Open that module, go to models
Open that model

Than add that function you’ve cooked up