<?php
require('classes/db.class.php');

$db = new db();
$date = date("Y-m");

$assets_res = $db->exec_query('assets', ['*'], '', '', '', '', "status='active'");

while ($asset = $assets_res->fetch_assoc()) {

    // echo $asset['record_id'] . "<br>";
    $open_jobcard_res = $db->exec_query("job_cards", ['*'], '', '', '', '', "asset_id = {$asset['record_id']} AND job_card_status = 'OPEN'");
    $open_data = $open_jobcard_res->num_rows;
    // echo "OPEN : " . $open_data . "<br>";
    $closed_jobcard_res = $db->exec_query("job_cards", ['*'], '', '', '', '', "asset_id = {$asset['record_id']} AND job_card_status = 'CLOSED'");
    $closed_data = $closed_jobcard_res->num_rows;
    // echo "CLOSED : " . $closed_data . "<br>";
    $awaiting_parts_jobcard_res = $db->exec_query("job_cards", ['*'], '', '', '', '', "asset_id = {$asset['record_id']} AND job_card_status = 'AWAITING PARTS'");
    $awaiting_parts_data = $awaiting_parts_jobcard_res->num_rows;
    // echo "AWAITING PARTS : " . $awaiting_parts_data . "<br>";
    // echo "DATE Data was captured" . $date;

    $db->insert("INSERT INTO `monthly_jobcard_assets_totals` (`asset_id`,`asset_type_id`,`job_card_open_total`,`job_card_closed_total`,`awaiting_parts_total`,`date`) VALUES ('{$asset['record_id']}','{$asset['asset_type_id']}','$open_data','$closed_data','$awaiting_parts_data','$date')");

}