table_name = $wpdb->prefix . 'acceve_certificate_verification'; add_action('admin_init', array($this, 'database_table_for_certificate_verification')); } /** * Certificate verification database table create. * * @return void * @since 1.0.0 */ public function database_table_for_certificate_verification() { global $wpdb; // phpcs:ignore if ($wpdb->get_var("SHOW TABLES LIKE '$this->table_name'") != $this->table_name) { $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE $this->table_name ( id mediumint(9) NOT NULL AUTO_INCREMENT, certificate_id varchar(20) NOT NULL, student_name varchar(100) NOT NULL, course_type varchar(50) NOT NULL, course_name varchar(100) NOT NULL, institution varchar(100) NOT NULL, issue_date date NOT NULL, expiry_date date DEFAULT NULL, verification_code varchar(32) NOT NULL, additional_data text DEFAULT NULL, is_active tinyint(1) DEFAULT 1, created_at datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY certificate_id (certificate_id), KEY verification_code (verification_code) ) $charset_collate;"; require_once(ABSPATH . 'wp-admin/includes/upgrade.php'); dbDelta($sql); self::add_sample_data(); } } /** * Helper function for inserting two sample data on database, when db table create. * * @return void * @since 1.0.0 */ private static function add_sample_data() { global $wpdb; $table_name = $wpdb->prefix . 'acceve_certificate_verification'; $sample_data = array( array( 'certificate_id' => 'DIP-' . wp_rand(1000, 9999), 'student_name' => 'John Doe', 'course_type' => 'Online', 'course_name' => 'Advanced Web Development', 'institution' => 'Tech University', 'issue_date' => gmdate('Y-m-d', strtotime('-1 month')), 'expiry_date' => gmdate('Y-m-d', strtotime('+2 years')), 'verification_code' => wp_generate_password(8, false), 'is_active' => 1 ), array( 'certificate_id' => 'UNI-' . wp_rand(1000, 9999), 'student_name' => 'Jane Smith', 'course_type' => 'Offline', 'course_name' => 'Computer Science', 'institution' => 'Global University', 'issue_date' => gmdate('Y-m-d', strtotime('-6 months')), 'expiry_date' => gmdate('Y-m-d', strtotime('+4 years')), 'verification_code' => wp_generate_password(8, false), 'is_active' => 1 ) ); foreach ($sample_data as $data) { // phpcs:ignore $wpdb->insert($table_name, $data); } } /** * Helper function for getting certificate information from db table. * * @return array | null | object * @since 1.0.0 */ public function get_certificate($certificate_id) { global $wpdb; // phpcs:ignore return $wpdb->get_row( // phpcs:ignore $wpdb->prepare("SELECT * FROM {$this->table_name} WHERE certificate_id = %s", $certificate_id) ); } /** * Helper function for adding certificate information into db table. * * @return bool | int | null * @since 1.0.0 */ public function add_certificate($data) { global $wpdb; $defaults = array( 'verification_code' => wp_generate_password(8, false), 'created_at' => current_time('mysql'), 'is_active' => 1 ); $data = wp_parse_args($data, $defaults); // phpcs:ignore return $wpdb->insert($this->table_name, $data); } /** * Helper function for updating certificate information into db table. * * @return bool | int | null * @since 1.0.0 */ public function update_certificate($original_id, $data) { global $wpdb; // phpcs:ignore return $wpdb->update( $this->table_name, $data, array('certificate_id' => $original_id), null, array('%s') ); } /** * Helper function for deleting certificate information from db table. * * @return bool | int | null * @since 1.0.0 */ public function delete_certificate($certificate_id) { global $wpdb; // phpcs:ignore return $wpdb->delete( $this->table_name, array('certificate_id' => $certificate_id), array('%s') ); } /** * Helper function for getting all certificate from db table for all certificate show on table. * * @return array | null | object * @since 1.0.0 */ public function get_all_certificates($per_page = 20, $page_number = 1) { global $wpdb; $offset = ($page_number - 1) * $per_page; // phpcs:ignore $sql = "SELECT * FROM {$this->table_name}"; // phpcs:ignore if (!empty($_REQUEST['orderby'])) { // phpcs:ignore $sql .= ' ORDER BY ' . esc_sql(sanitize_text_field(wp_unslash($_REQUEST['orderby']))); // phpcs:ignore $sql .= !empty($_REQUEST['order']) ? ' ' . esc_sql(sanitize_text_field(wp_unslash($_REQUEST['order']))) : ' ASC'; } else { // phpcs:ignore $sql .= ' ORDER BY created_at DESC'; } // phpcs:ignore $sql .= " LIMIT %d OFFSET %d"; // phpcs:ignore return $wpdb->get_results( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Already prepared above $wpdb->prepare($sql, $per_page, $offset), ARRAY_A ); } /** * Helper function for getting certificate count number from db table. * * @return null | string * @since 1.0.0 */ public function count_certificates() { global $wpdb; // phpcs:ignore return $wpdb->get_var("SELECT COUNT(*) FROM {$this->table_name}"); } }