<?php
die('Invailid Url');
set_time_limit(0);
ini_set('memory_limit','1024M');
ini_set('post_max_size','32M');
ini_set('upload_max_filesize ','32M');

$destdir  = "live-data-migration-csvs/";
$name     = "work_order_planning.csv";
$destfile = $destdir.$name;  

$batch_size = 3000;
$table_name = "production_planning";
$records_inserted = 0;
$records_updated = 0;  
                                   
$flag = true;
$fp = fopen($destfile, 'r');
while (!feof ($fp)){
  $buffer = fgetcsv($fp, 1000000, ',');  
  if($flag) { 
	  if($buffer){ 
	  
	        $variable_array = array("part_number","CustomersRef","ReqDate","ReqQty");
			foreach($variable_array as $key=>$val){		
				${$val} = addslashes(trim($buffer[$key]));
			}
			if($part_number =="part_number"){
				continue;
			}
						
			$ReqDate 			= date('Y-m-d', strtotime($ReqDate));
			$created_by					= $_SESSION['user_id'];
			$created_on					= date("Y-m-d H:i:s");	
			$updated_by					= $_SESSION['user_id'];
			/* $coun_qry = "SELECT id FROM $table_name WHERE part_number = '$part_number'";
			$coun_sql = mysqli_query($conn,$coun_qry);
			$part_number_exist_id = "";
			if(mysqli_num_rows($coun_sql)>0){
				$data1 = mysqli_fetch_array($coun_sql);
				$part_number_exist_id = $data1['id'];
				
				$sql_update = "part_number = '".$part_number."',customer_reff = '".$CustomersRef."',req_date = '".$ReqDate."',req_qty = '".$ReqQty."', updated_by = '".$updated_by."' ";
				$sql_update = "UPDATE $table_name SET ".$sql_update." WHERE id = '".$part_number_exist_id."'";
				if(mysqli_query($conn,$sql_update)){
					$records_updated++;
				}					
			}else{	 */		
				
if (!function_exists('getBoughtInPartsOnly')) {
    function getBoughtInPartsOnly($product_ids, $order_quantities, $conn, $required_date, $work_order_id, $depth = 0, $max_depth = 10) {
        $bought_in_parts = [];

        for ($i = 0; $i < count($product_ids); $i++) {
            $product_id = $product_ids[$i];
            $quantity_needed = $order_quantities[$i];

            // Get BOM for this product
            $query = "SELECT sub_part_id, quantity_required, type FROM bom WHERE main_product_id = $product_id";
            $bom_parts = $conn->query($query);

            if ($bom_parts && $bom_parts->num_rows > 0) {
                while ($bom = $bom_parts->fetch_assoc()) {
                    $sub_part_id = $bom['sub_part_id'];
                    $sub_quantity_needed = $quantity_needed * $bom['quantity_required'];
                    $type = $bom['type'];

                    if ($type === "Bought In") {
                        if (!isset($bought_in_parts[$sub_part_id])) {
                            $bought_in_parts[$sub_part_id] = 0;
                        }
                        $bought_in_parts[$sub_part_id] += $sub_quantity_needed;

                        // Insert into purchase_list
                        echo $insert_query = "INSERT INTO purchase_list (item_id, quantity_requested, required_date, work_order_id) 
                                         VALUES ($sub_part_id, $sub_quantity_needed, '$required_date', $work_order_id)";
                        $conn->query($insert_query);
                    } elseif (($type === "Manufactured" || $type === "Assembly") && $depth < $max_depth) {
                        // Recursive call
                        $sub_parts = getBoughtInPartsOnly(
                            [$sub_part_id],
                            [$sub_quantity_needed],
                            $conn,
                            $required_date,
                            $work_order_id,
                            $depth + 1,
                            $max_depth
                        );
                        foreach ($sub_parts as $sp_id => $qty) {
                            if (!isset($bought_in_parts[$sp_id])) {
                                $bought_in_parts[$sp_id] = 0;
                            }
                            $bought_in_parts[$sp_id] += $qty;
                        }
                    }
                }
            }
        }

        return $bought_in_parts;
    }
}

// Example usage
$part_number_id = utility::getTableFieldById("item_management", "id", "part_number", $part_number);
$part_number_array = [$part_number_id];
$ReqQty_array = [$ReqQty];

// You must define these variables before calling the function
// Example:
//$required_date = '2025-06-10'; // format: YYYY-MM-DD
$work_order_id = 123; // example work order ID

$bought_in = getBoughtInPartsOnly($part_number_array, $ReqQty_array, $conn, $ReqDate, $work_order_id);
print_r($bought_in);
die;

				$sql = "part_number = '".$part_number_id."', customer_reff = '".$CustomersRef."',req_date = '".$ReqDate."',req_qty = '".$ReqQty."', created_on = '".$created_on."', created_by = '".$created_by."', updated_by = '".$updated_by."' ";
				$sql= "INSERT INTO $table_name SET ".$sql;
				
				if(mysqli_query($conn,$sql)){
					$records_inserted++;
				}
			//}
	  }
  }
}
?>
<?php 
if($records_inserted){    
  echo '<br><span style="font-family:calibri;color:red;">'.($records_inserted).' records has been inserted...</span>';
}?>
 <?php 
if($records_updated){    
  echo '<br><span style="font-family:calibri;color:red;">'.($records_updated).' records has been updated...</span>';
}?>