check_table_structure(); } /** * Gibt den internen Tabellenname inkl. Prefix für _offertable zurück * @return string Der Tabellenname */ function get_listtable_tablename() { global $wpdb; return ( $wpdb->prefix . 'atkp_lists' );; } public function exists_table() { global $wpdb; $tablename = $this->get_listtable_tablename(); $result = $wpdb->get_results( $wpdb->prepare( "SHOW TABLES LIKE %s", $tablename ) ); return array( count( $result ) > 0, $tablename ); } /** * 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_lists' ); //$current_version_db = 0; $table_name = $this->get_listtable_tablename(); $charset_collate = ATKPTools::get_wp_charset_collate(); //older or not even set if ( $current_version_db < ATKP_VERSION_LISTTABLE || $current_version_db == false || $override ) { $sql = "CREATE TABLE $table_name ( `id` bigint (50) NOT NULL AUTO_INCREMENT, `list_id` bigint (50) NOT NULL, `shop_id` bigint( 50 ) NULL, `product_id` bigint( 50 ) NULL, `list_idx` int(1) NOT 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, `updatedon` datetime NULL , `updatedmessage` text 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: die alten ALTER-Statements liefen bei einem Versionssprung erneut und //scheiterten mit "Duplicate key name" (gleiche Ursache wie bei der Produkttabelle) $this->create_index_if_missing( $table_name, $table_name . '_idx_list_id', '( `list_id` )', 'list_id' ); $this->create_index_if_missing( $table_name, $table_name . '_idx_product_id', '( `product_id` )', 'product_id' ); update_option( ATKP_PLUGIN_PREFIX . '_version_posts_lists', ATKP_VERSION_LISTTABLE ); } } /** * 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. '( `list_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; $exists = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$table_name} WHERE Key_name = %s", $index_name ) ); if ( $exists !== null ) { return; } if ( $leading_column !== '' ) { $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; } } $wpdb->query( "CREATE INDEX {$index_name} ON {$table_name} {$columns}" ); } public function clear_list( $list_id ) { global $wpdb; $table_name = $this->get_listtable_tablename(); $affected = $wpdb->query( $wpdb->prepare( "DELETE FROM {$table_name} WHERE list_id=%d", $list_id ) ); return $affected; } public function save_list( $list_id, $products ) { global $wpdb; $table_name = $this->get_listtable_tablename(); $this->clear_list( $list_id ); $list_idx = 1; foreach ( $products as $product ) { $data = $this->get_array_from_product( $list_id, $list_idx ++, $product, false ); $wpdb->insert( $table_name, $data ); if ( $wpdb->last_error !== '' || $wpdb->insert_id == 0 ) { $data = $this->get_array_from_product( $list_id, $list_idx ++, $product, true ); $result = $wpdb->insert( $table_name, $data ); } $id = $wpdb->insert_id; } } public function save_productlist( $list_id, $shop_id, $products ) { global $wpdb; $table_name = $this->get_listtable_tablename(); foreach ( $products as $product ) { $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( $list_id, 0, $product, false ); $data['shop_id'] = $shop_id; $wpdb->insert( $table_name, $data ); if ( $wpdb->last_error !== '' ) { $data = $this->get_array_from_product( $list_id, 0, $product, true ); $data['shop_id'] = $shop_id; $wpdb->insert( $table_name, $data ); } $id = $wpdb->insert_id; } } public function load_list( $list_id, $shop_id = '' ) { global $wpdb; $table_name = $this->get_listtable_tablename(); if ( $shop_id != '' ) { $query = $wpdb->prepare( "SELECT * FROM $table_name WHERE list_id = %d and shop_id = %d order by list_idx ", $list_id, $shop_id ); } else { $query = $wpdb->prepare( "SELECT * FROM $table_name WHERE list_id = %d order by list_idx ", $list_id ); } $result = $wpdb->get_results( $query, ARRAY_A ); return $this->get_product_from_array( $result ); } private function get_array_from_product( $list_id, $list_idx, $productarray, $remove_character = false ) { if ( is_a( $productarray, 'atkp_product' ) ) { $prd = $productarray; $productarray = array(); $productarray['type'] = 'product'; $productarray['value'] = $prd; } $type = $productarray['type']; if ( $type == 'product' ) { /** @var atkp_product $atkp_product */ $atkp_product = $productarray['value']; $data = array( 'list_id' => $list_id, 'list_idx' => $list_idx, 'updatedon' => gmdate( "Y-m-d H:i:s" ), 'shop_id' => $atkp_product->shopid, 'title' => $remove_character ? $this->fix_sql_field( $atkp_product->title ) : $atkp_product->title, 'description' => $remove_character ? $this->fix_sql_field( $atkp_product->description ) : $atkp_product->description, 'asin' => $atkp_product->asin, 'parentasin' => $atkp_product->parentasin, 'ean' => ( strlen( $atkp_product->ean ) > 250 ? substr( $atkp_product->ean, 0, 250 ) : $atkp_product->ean ), 'isbn' => $atkp_product->isbn, 'gtin' => $atkp_product->gtin, 'mpn' => $atkp_product->mpn, 'brand' => $atkp_product->brand, 'productgroup' => $atkp_product->productgroup, 'releasedate' => $atkp_product->releasedate, 'addtocarturl' => $atkp_product->addtocarturl, 'producturl' => $atkp_product->producturl, 'smallimageurl' => $atkp_product->smallimageurl, 'mediumimageurl' => $atkp_product->mediumimageurl, 'largeimageurl' => $atkp_product->largeimageurl, 'images' => $atkp_product->images == null ? null : serialize( $atkp_product->images ), 'manufacturer' => $atkp_product->manufacturer, 'author' => $atkp_product->author, 'numberofpages' => $atkp_product->numberofpages, 'features' => $remove_character ? $this->fix_sql_field( $atkp_product->features ) : $atkp_product->features, 'variations' => $atkp_product->variations == null ? null : serialize( $atkp_product->variations ), 'variationname' => $atkp_product->variationname == null ? null : serialize( $atkp_product->variationname ), 'rating' => $atkp_product->rating, 'reviewcount' => $atkp_product->reviewcount, 'customerreviewurl' => $atkp_product->customerreviewurl, 'listprice' => $atkp_product->listprice, 'listpricefloat' => $atkp_product->listpricefloat, 'amountsaved' => $atkp_product->amountsaved, 'amountsavedfloat' => $atkp_product->amountsavedfloat, 'percentagesaved' => $atkp_product->percentagesaved, 'percentagesavedfloat' => $atkp_product->percentagesaved, 'saleprice' => $atkp_product->saleprice, 'salepricefloat' => $atkp_product->salepricefloat, 'shipping' => $atkp_product->shipping, 'shippingfloat' => $atkp_product->shippingfloat, 'availability' => $atkp_product->availability, 'isprime' => $atkp_product->isprime, 'iswarehouse' => $atkp_product->iswarehouse, 'baseprice' => $atkp_product->baseprice, 'basepricefloat' => $atkp_product->basepricefloat, 'baseunit' => $atkp_product->baseunit, 'haserror' => $atkp_product->haserror, ); } else if ( $type == 'productid' ) { $productid = $productarray['value']; $data = array( 'list_id' => $list_id, 'product_id' => intval( $productid ), 'list_idx' => $list_idx, 'updatedon' => gmdate( "Y-m-d H:i:s" ), ); } else { /* translators: %s: product type name */ throw new Exception( esc_html( sprintf( __( 'unknown producttype: %s', 'affiliate-toolkit-starter' ), $type ) ) ); } $data = apply_filters( 'atkp_modify_list_before_db_write', $data ); //info: wpdb verwirft den gesamten Datensatz, wenn ein einziger Wert nicht in seine Spalte // passt. Der zweite Versuch mit fix_sql_field() in save_list() faengt nur // Zeichensatzprobleme ab, nicht die Laenge - das Produkt fehlte dann still in der Liste. $data = ATKPTools::truncate_row_for_table( $this->get_listtable_tablename(), $data, array( 'images', 'variations', 'variationname' ) ); return $data; } private function fix_sql_field( $value, $maxlength = 0 ) { if ( $value == '' ) { return ''; } //removing unused strings $value = strip_tags( $value, [ '', '
', '