check_table_structure(); } /** * Gibt den internen Tabellenname inkl. Prefix für _offertable zurück * @return string Der Tabellenname */ function get_producttable_tablename() { global $wpdb; return $wpdb->prefix . 'atkp_products'; } public function exists_table() { global $wpdb; $tablename = $this->get_producttable_tablename(); $result = $wpdb->get_results( $wpdb->prepare( "SHOW TABLES LIKE %s", $tablename ) ); return array( count( $result ) > 0, $tablename ); } /** * Count products by shop ID with error statistics * * @param int $shop_id * * @return array Array with 'total', 'error' and 'success' counts */ public function count_products_by_shop_with_stats( $shop_id ) { $stats = $this->count_products_by_shop_with_stats_all(); $shop_id = (int) $shop_id; if ( isset( $stats[ $shop_id ] ) ) { return $stats[ $shop_id ]; } return array( 'total' => 0, 'error' => 0, 'success' => 0 ); } /** * Zaehlt die Produkte aller Shops in einer einzigen Abfrage. * * Die Shop-Uebersicht rendert diese Zahlen pro Zeile. Vorher lief dafuer eine eigene * Abfrage je Shop, was bei 20 Shops 20 Abfragen ueber die gesamte Produkttabelle waren. * Das Ergebnis wird fuer die Dauer des Requests gehalten, weil die Spalte es fuer jede * Zeile erneut anfragt. * * @return array shop_id => array( 'total', 'error', 'success' ) */ public function count_products_by_shop_with_stats_all() { if ( self::$shop_stats_cache !== null ) { return self::$shop_stats_cache; } global $wpdb; $table_name = $this->get_producttable_tablename(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Tabellenname aus $wpdb, keine Nutzereingabe. $rows = $wpdb->get_results( "SELECT shop_id, COUNT(*) as total, SUM(CASE WHEN haserror = 1 THEN 1 ELSE 0 END) as error, SUM(CASE WHEN haserror = 0 OR haserror IS NULL THEN 1 ELSE 0 END) as success FROM {$table_name} GROUP BY shop_id", ARRAY_A ); $stats = array(); if ( is_array( $rows ) ) { foreach ( $rows as $row ) { $stats[ (int) $row['shop_id'] ] = array( 'total' => (int) $row['total'], 'error' => (int) $row['error'], 'success' => (int) $row['success'], ); } } self::$shop_stats_cache = $stats; return $stats; } /** * Leert den Request-Cache der Shop-Statistik. */ public static function clear_shop_stats_cache() { self::$shop_stats_cache = null; } private static $shop_stats_cache = null; /** * Legt einen Index nur an, wenn er noch nicht existiert. * * @param string $table_name Tabellenname. * @param string $index_name Name des Index. * @param string $columns Spaltenliste inkl. Klammern, z.B. '( `shop_id` )'. * @param string $leading_column Optional: existiert bereits ein beliebiger Index mit dieser * Spalte an erster Position, wird kein zweiter angelegt. */ private function create_index_if_missing( $table_name, $index_name, $columns, $leading_column = '' ) { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Tabellenname aus $wpdb. $exists = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$table_name} WHERE Key_name = %s", $index_name ) ); if ( $exists !== null ) { return; } if ( $leading_column !== '' ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Tabellenname aus $wpdb. $exists = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$table_name} WHERE Seq_in_index = 1 AND Column_name = %s", $leading_column ) ); if ( $exists !== null ) { return; } } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Tabellenname aus $wpdb. $wpdb->query( "CREATE INDEX {$index_name} ON {$table_name} {$columns}" ); } public function get_product_ids_with_error() { global $wpdb; $table_name = $this->get_producttable_tablename(); // Produkte mit haserror = 1 in der Produkttabelle $results = $wpdb->get_col( "SELECT DISTINCT product_id FROM {$table_name} WHERE haserror = 1" ); $ids = ! empty( $results ) ? array_map( 'intval', $results ) : array(); // Produkte mit Fehlermeldung in Post-Meta (z.B. wenn keine Sub-Produkte gefunden wurden) $meta_key = ATKP_PRODUCT_POSTTYPE . '_message'; $meta_results = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id WHERE p.post_type = %s AND pm.meta_key = %s AND pm.meta_value IS NOT NULL AND pm.meta_value != ''", ATKP_PRODUCT_POSTTYPE, $meta_key ) ); if ( ! empty( $meta_results ) ) { $ids = array_merge( $ids, array_map( 'intval', $meta_results ) ); } $ids = array_values( array_unique( $ids ) ); return $ids; } public function get_product_ids_without_saleprice() { global $wpdb; $table_name = $this->get_producttable_tablename(); // Alle distinct product_id holen, die haserror = 1 haben $results = $wpdb->get_col( "SELECT DISTINCT product_id FROM {$table_name} WHERE salepricefloat is null" ); if ( empty( $results ) ) { return array(); } // In Integer casten und Duplikate entfernen (sicherheitshalber) $ids = array_map( 'intval', $results ); $ids = array_values( array_unique( $ids ) ); return $ids; } /** * Prüft ob die Tabelle atkp_offertable vorhanden ist, und legt diese ggf. an. */ public function check_table_structure( $override = false ) { global $wpdb; require_once( ABSPATH . 'wp-admin/includes/upgrade.php' ); //required for dbDelta $current_version_db = get_option( ATKP_PLUGIN_PREFIX . '_version_posts_products' ); //$current_version_db = 0; $table_name = $this->get_producttable_tablename(); $charset_collate = ATKPTools::get_wp_charset_collate(); //older or not even set if ( $current_version_db < ATKP_VERSION_PRODUCTTABLE || $current_version_db == false || $override ) { $sql = "CREATE TABLE $table_name ( `id` bigint (50) NOT NULL AUTO_INCREMENT, `product_id` bigint( 50 ) NOT NULL, `shop_id` bigint( 50 ) NULL, `queue_id` bigint( 50 ) NULL, `title` varchar(700) NULL , `description` text NULL , `asin` varchar( 250 ) NULL, `parentasin` varchar( 250 ) NULL, `ean` varchar( 250 ) NULL, `isbn` varchar( 250 ) NULL, `gtin` varchar( 250 ) NULL, `mpn` varchar( 250 ) NULL, `brand` varchar( 250 ) NULL, `productgroup` varchar( 600 ) NULL, `releasedate` varchar( 250 ) NULL, `addtocarturl` varchar( 1200 ) NULL, `producturl` varchar( 1200 ) NULL, `smallimageurl` varchar( 600 ) NULL, `mediumimageurl` varchar( 600 ) NULL, `largeimageurl` varchar( 600 ) NULL, `images` text NULL, `manufacturer` varchar( 250 ) NULL, `author` varchar( 250 ) NULL, `numberofpages` int(20) NULL , `features` text NULL, `variations` text NULL, `variationname` text NULL, `rating` decimal(20,2) NULL , `reviewcount` int(20) NULL , `customerreviewurl` varchar( 600 ) NULL, `listprice` varchar( 250 ) NULL, `listpricefloat` decimal(20,2) NULL , `amountsaved` varchar( 250 ) NULL, `amountsavedfloat` decimal(20,2) NULL , `percentagesaved` varchar( 250 ) NULL, `percentagesavedfloat` decimal(20,2) NULL , `saleprice` varchar( 250 ) NULL, `salepricefloat` decimal(20,2) NULL , `shipping` varchar( 250 ) NULL , `shippingfloat` decimal(20,2) NULL , `availability` varchar( 250 ) NULL, `isprime` int(1) NULL , `iswarehouse` int(1) NULL, `cpc` varchar( 150 ) NULL, `cpcfloat` decimal(20,2) NULL , `updatedon` datetime NULL , `updatedmessage` text NULL, `isupdated` int(1) NULL , `haserror` int(1) NULL , `baseprice` varchar( 150 ) NULL, `basepricefloat` decimal(20,2) NULL, `baseunit` varchar( 150 ) NULL, PRIMARY KEY (id) ) $charset_collate"; dbDelta( $sql ); //idempotent: bei einem Versionssprung liefen die alten ALTER-Statements erneut und //scheiterten mit "Duplicate key name" (gleiche Ursache wie bei den Queue-Indizes) $this->create_index_if_missing( $table_name, $table_name . '_idx_product_id', '( `product_id` )', 'product_id' ); $this->create_index_if_missing( $table_name, $table_name . '_idx_ean', '( `ean` )', 'ean' ); $this->create_index_if_missing( $table_name, $table_name . '_idx_asin', '( `asin` )', 'asin' ); //ohne diesen Index ist jede Auswertung je Shop ein Full Scan ueber die gesamte //Produkttabelle - auf der Shop-Uebersicht einmal pro Shop-Zeile $this->create_index_if_missing( $table_name, $table_name . '_idx_shop_id', '( `shop_id` )', 'shop_id' ); update_option( ATKP_PLUGIN_PREFIX . '_version_posts_products', ATKP_VERSION_PRODUCTTABLE ); } } public function clear_products( $product_id ) { global $wpdb; $table_name = $this->get_producttable_tablename(); $affected = $wpdb->query( $wpdb->prepare( "update {$table_name} set isupdated = 0 WHERE product_id=%d", $product_id ) ); return $affected; } public function clear_products_importset( $product_id ) { global $wpdb; $table_name = $this->get_producttable_tablename(); $affected = $wpdb->query( $wpdb->prepare( "update {$table_name} set isupdated = 1 WHERE product_id=%d and importset_key is not null", $product_id ) ); $affected = $wpdb->query( $wpdb->prepare( "update {$table_name} set isupdated = 0 WHERE product_id=%d and importset_key is null", $product_id ) ); return $affected; } public function get_sub_products( $product_id, $was_updated = false ) { global $wpdb; $table_name = $this->get_producttable_tablename(); $result = $wpdb->get_results( $wpdb->prepare( "select id from {$table_name} WHERE product_id=%d and isupdated " . ( $was_updated ? '=' : '<>' ) . " 1", $product_id ), ARRAY_A ); $post_ids = array(); if ( $result ) { foreach ( $result as $row ) { $post_ids[] = $row['id']; } } return $post_ids; } public function delete_unused_products( $product_id ) { global $wpdb; $table_name = $this->get_producttable_tablename(); $affected = $wpdb->query( $wpdb->prepare( "delete from {$table_name} WHERE product_id=%d and isupdated <> 1", $product_id ) ); return $affected; } public function delete_old_productdata( $product_id ) { global $wpdb; $table_name = $this->get_producttable_tablename(); $affected = $wpdb->query( $wpdb->prepare( "update {$table_name} set listprice = null, listpricefloat = null, amountsaved = null, amountsavedfloat = null, percentagesaved =null, percentagesavedfloat = null, saleprice = null, salepricefloat = null, shipping = null, shippingfloat= null, availability = null, isprime = 0, cpc = null, cpcfloat = null, baseprice = null, basepricefloat = null WHERE product_id=%d and isupdated <> 1", $product_id ) ); return $affected; } public function load_products_by_asin( $asin, $shop_id = '' ) { global $wpdb; $table_name = $this->get_producttable_tablename(); if ( $shop_id != '' ) { $query = $wpdb->prepare( "SELECT product_id FROM $table_name inner join {$wpdb->posts} p on p.ID = product_id WHERE asin = %s and shop_id = %d GROUP BY product_id", $asin, intval( $shop_id ) ); } else { $query = $wpdb->prepare( "SELECT product_id FROM $table_name inner join {$wpdb->posts} p on p.ID = product_id WHERE asin = %s GROUP BY product_id", $asin ); } $result = $wpdb->get_results( $query, ARRAY_A ); $post_ids = array(); if ( $result ) { foreach ( $result as $row ) { $post_ids[] = $row['product_id']; } } return $post_ids; } public function load_products_by_ean( $ean ) { global $wpdb; $table_name = $this->get_producttable_tablename(); $query = $wpdb->prepare( "SELECT product_id FROM $table_name inner join {$wpdb->posts} p on p.ID = product_id WHERE ean like %s GROUP BY product_id", '%' . $wpdb->esc_like( $ean ) . '%' ); $result = $wpdb->get_results( $query, ARRAY_A ); $post_ids = array(); if ( $result ) { foreach ( $result as $row ) { $post_ids[] = $row['product_id']; } } return $post_ids; } public function load_products_by_title( $title ) { global $wpdb; $table_name = $this->get_producttable_tablename(); $query = $wpdb->prepare( "SELECT product_id FROM $table_name inner join {$wpdb->posts} p on p.ID = product_id WHERE title like %s GROUP BY product_id", '%' . $wpdb->esc_like( $title ) . '%' ); $result = $wpdb->get_results( $query, ARRAY_A ); $post_ids = array(); if ( $result ) { foreach ( $result as $row ) { $post_ids[] = $row['product_id']; } } return $post_ids; } public function exists_product( $product_id, $shop_id ) { global $wpdb; $table_name = $this->get_producttable_tablename(); $query = $wpdb->prepare( "SELECT count(*) as cnt FROM $table_name WHERE product_id = %d and shop_id = %d ", $product_id, $shop_id ); $result = $wpdb->get_results( $query, ARRAY_A ); $cnt = count( $result ) > 0 ? intval( $result[0]['cnt'] ) : 0; if ( $cnt >= 1 ) { return true; } else { return false; } } public function save_products( $product_id, $shop_id, $queue_id, $products ) { global $wpdb; $table_name = $this->get_producttable_tablename(); foreach ( $products as $product ) { $product->shopid = $shop_id; $product->title = html_entity_decode( $product->title ); $product->features = html_entity_decode( $product->features ); $product->description = html_entity_decode( $product->description ); $product->productgroup = html_entity_decode( $product->productgroup ); $data = $this->get_array_from_product( $product_id, $queue_id, $product, false ); if ( $this->exists_product( $product_id, $shop_id ) ) { $wpdb->update( $table_name, $data, array( 'shop_id' => $shop_id, 'product_id' => $product_id ) ); $product->id = $product_id; } else { $wpdb->insert( $table_name, $data ); $product->id = $wpdb->insert_id; } if ( $wpdb->last_error !== '' || $product->id == 0 ) { //wenn ein Fehler aufgetreten ist, dann sollte man die beschreibungen mal fixen $data = $this->get_array_from_product( $product_id, $queue_id, $product, true ); if ( $this->exists_product( $product_id, $shop_id ) ) { $wpdb->update( $table_name, $data, array( 'shop_id' => $shop_id, 'product_id' => $product_id ) ); } else { $wpdb->insert( $table_name, $data ); $product->id = $wpdb->insert_id; } } } } /** * Load Products * * @param $product_id * * @return atkp_product[] */ public function load_products( $product_id ) { global $wpdb; $table_name = $this->get_producttable_tablename(); $query = $wpdb->prepare( "SELECT * FROM $table_name WHERE product_id = %d ", $product_id ); $result = $wpdb->get_results( $query, ARRAY_A ); return $this->get_product_from_array( $result ); } private function fix_sql_field( $value, $maxlength = 0 ) { if ( $value == '' ) { return ''; } //removing unused strings $value = strip_tags( $value, [ '', '
', '