<?php 
	
add_action('woocommerce_thankyou', 'copy_order_to_tbl', 10, 1);
function copy_order_to_tbl($order_id){

global $wpdb;

if ( ! $order_id )
return;

global $wpdb;

// Define the database credentials for the other database
$other_db_name = 'ideezeur_yogibook';
$other_db_user = 'ideezeur_wp781';
$other_db_password = 'Sk4@Rb]pFQ4(!9)E';
$other_db_host = 'localhost';

$other_db = new wpdb($other_db_user, $other_db_password, $other_db_name, $other_db_host);
$conn = new mysqli($other_db_host, $other_db_user, $other_db_password, $other_db_name);

$tablename = 'copyorder';
$Itemstablename = 'copyorder_items';
$yogibooktableorder = 'orderbook';

$query = $wpdb->prepare("SELECT * FROM $tablename WHERE order_id=%s;", $order_id );
$row = $wpdb->get_row( $query );

if($row)
return;

$order = wc_get_order( $order_id );
$order_data = $order->get_data();

$copyOrder = [];

$copyOrder['order_id'] = $order_data['id'];
$copyOrder['order_parent_id'] = $order_data['parent_id'];
$copyOrder['order_status'] = $order_data['status'];


$copyOrder['order_date_created'] = $order_data['date_created']->date('Y-m-d H:i:s');
$copyOrder['order_date_modified'] = $order_data['date_modified']->date('Y-m-d H:i:s');


$copyOrder['order_total'] = $order_data['total'];

$copyOrder['order_customer_id'] = $order_data['customer_id'];

$copyOrder['order_billing_first_name'] = $order_data['billing']['first_name'];
$copyOrder['order_billing_last_name'] = $order_data['billing']['last_name'];

$copyOrder['order_billing_email'] = $order_data['billing']['email'];

//$other_db->insert( $tablename, $copyOrder);

foreach ($order->get_items() as $item_key => $item ):
$copyOrderItem = [];

$item_data = $item->get_data();
//$item_data = $item->get_data();
$copyOrderItemyogi['order_id'] = $order_data['id'];
$copyOrderItemyogi['order_date_created'] = $order_data['date_created']->date('Y-m-d H:i:s');
$copyOrderItemyogi['order_total'] = $order_data['total'];
$copyOrderItemyogi['order_billing_email'] = $order_data['billing']['email'];
$copyOrderItemyogi['product_name'] = $item_data['name'];
$copyOrderItemyogi['product_id'] = $item_data['product_id'];
$copyOrderItemyogi['variation_id'] = $item_data['variation_id'];
$copyOrderItemyogi['quantity'] = $item_data['quantity'];
$copyOrderItemyogi['line_total'] = $item_data['total'];
$copyOrderItemyogi['first_name'] = $order_data['billing']['first_name'];
$copyOrderItemyogi['last_name'] = $order_data['billing']['last_name'];

// Check if product has variation.

if ($item_data['variation_id']) {
$product = wc_get_product($item_data['variation_id']);
} else {
$product = wc_get_product($item_data['product_id']);
}


// query to find idservice
$productvar= $item_data['variation_id'];
$checkservice = "SELECT idservice
          FROM service
          WHERE FIND_IN_SET(?, wpcatalognumber) > 0";

$result = $conn->prepare($checkservice);
$result->bind_param("s", $productvar);
$result->execute();
$result->store_result();

if ($result->num_rows > 0) {
    $result->bind_result($idservice);
    $result->fetch();
    $copyOrderItemyogi['idservice'] = $idservice;
} else {
    $copyOrderItemyogi['idservice'] = "0";
}

//end query

$copyOrderItemyogi['cod'] = $product->get_sku();
// Dividi la stringa utilizzando il carattere '_'
$product_sku = $product->get_sku();
$parts = explode('_', $product_sku);

// Assegna i valori alle variabili richieste
$classshort = $parts[0];     // aerial
$quantityclass = $parts[1]; // 4
$dayclass = $parts[2];      // Mar

$copyOrderItemyogi['classshort'] = $classshort;
$copyOrderItemyogi['quantityclass'] = $quantityclass;
$copyOrderItemyogi['nticket'] = $quantityclass;
$copyOrderItemyogi['dayclass'] = $dayclass;
$copyOrderItemyogi['status'] = "pending";


$other_db->insert( $yogibooktableorder, $copyOrderItemyogi);

endforeach;

}

?>