사분기는 1,2,3,4만 나오니까 작년도로 바뀔때 연도까지 갱신해줄 필요가 있다

위 처럼 코딩하면 작년도의 사분기를 체크할 수 없을 것 같은데

오늘을 예로들으면 $currentQuarter 값이 1로 나올텐데(1사분기 이므로), 그렇게되면 for ($i = 1; $i <= $currentQuarter; $i++)  구문이 잘 작동하지 않을 것 같다

php에 quarter함수가 없는것 같은데
ChatGPT
죄송합니다. MySQL에서 제공하는 QUARTER 함수가 아니라, PHP에서는 date 함수와 strtotime 함수를 이용하여 현재 날짜의 분기를 계산할 수 있습니다. 코드를 수정하여 이용해보겠습니다.

<?php
$servername = "your_mysql_server";
$username = "your_mysql_username";
$password = "your_mysql_password";
$dbname = "your_database_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
}

$currentYear = date('Y');  // 현재 년도
$currentQuarter = ceil(date('n') / 3); // 현재 분기

// Function to calculate average cost per gram
function calculateAverageCost($totalCost, $totalQuantity) {
   if ($totalQuantity > 0) {
       return $totalCost / $totalQuantity;
   } else {
       return 0;
   }
}

// Initialize the start and end dates for the first quarter
$startThisQuarterDate = new DateTime("$currentYear-01-01");
$endThisQuarterDate = new DateTime("$currentYear-03-31");

// Iterate over all quarters until data is found or until we reach the first quarter of the year 1970
while ($startThisQuarterDate->format('Y-m-d') >= '1970-01-01') {
   $avgPrevCostPerGram = 0;

   // Get the data for the previous quarter
   $sql = "SELECT uh.herbname,
                  uh.price * uh.quantity as total_prev_cost,
                  uh.quantity as total_prev_quantity
           FROM used_herb uh
           WHERE uh.date >= '" . $startThisQuarterDate->format('Y-m-d H:i:s') . "'
               AND uh.date <= '" . $endThisQuarterDate->format('Y-m-d H:i:s') . "'";

   $result = $conn->query($sql);

   if ($result->num_rows > 0) {
       $totalPrevCost = 0;
       $totalPrevQuantity = 0;
       while ($row = $result->fetch_assoc()) {
           $totalPrevCost += $row["total_prev_cost"];
           $totalPrevQuantity += $row["total_prev_quantity"];
       }
       $avgPrevCostPerGram = calculateAverageCost($totalPrevCost, $totalPrevQuantity);

       // Update the herb table with the calculated average cost per gram for the matching herbname
       $updateSql = "UPDATE herb
                     SET cost_per_gram = $avgPrevCostPerGram
                     WHERE herbname IN (
                         SELECT DISTINCT uh.herbname
                         FROM used_herb uh
                         WHERE uh.date >= '" . $startThisQuarterDate->format('Y-m-d H:i:s') . "'
                             AND uh.date <= '" . $endThisQuarterDate->format('Y-m-d H:i:s') . "'
                     )";
       $conn->query($updateSql);

       echo "Herb table updated successfully for quarter ending on {$endThisQuarterDate->format('Y-m-d')}.";
       break;  // Break the loop once data is found and updated
   }

   // Move to the previous quarter
   $startThisQuarterDate->modify('-3 months');
   $endThisQuarterDate->modify('-3 months');
}

$conn->close();
?>