<?php
/*
DB Manager for WP FILE MANAGER
*/
define('DB_MANAGER_VERSION', '1.0');
if (!class_exists('mk_db_manager')):
class mk_db_manager
{
    /*
    Constructor
    */
    public function __construct()
    {
        add_action('admin_menu', array(&$this, 'db_manager_menu_page'));
        add_action('admin_enqueue_scripts', array(&$this, 'ffm_db_things'));
        add_action('wp_ajax_Update_Tbl_Record', array(&$this, 'UpdateTblRecord'));
        add_action('admin_post_mk_db_manager_export_db', array($this, 'export_db'));
    }

    /*
    * Checking WP File Manager Plugin is Activated or not.
    */
    public static function wp_file_manager_exists($type = '')
    {
        if (!function_exists('is_plugin_active')) {
            include_once ABSPATH.'wp-admin/includes/plugin.php';
        }
        if (!empty($type) && $type == 'pro') {
            $checkPlugin = 'wp-file-manager-pro/file_folder_manager_pro.php';
        } else {
            $checkPlugin = 'wp-file-manager/file_folder_manager.php';
        }
        if (is_plugin_active($checkPlugin)) {
            return true;
        } else {
            return false;
        }
    }

    /*
    Scripts
    */
    public function ffm_db_things()
    {
        $getPage = isset($_GET['page']) ? $_GET['page'] : '';
        $allowedPages = array(
                                      'wp_file_manager_db_manager',
                                      );
        if (!empty($getPage) && in_array($getPage, $allowedPages)):
                        wp_enqueue_script('jquery-ui-script', plugins_url('js/jquery-ui.js', __FILE__));
        wp_enqueue_style('jquery-ui-style', plugins_url('css/jquery-ui.css', __FILE__));
        wp_enqueue_script('fm_db_manager-script', plugins_url('js/fm_db_manager.js', __FILE__));
        wp_enqueue_style('fm_db_manager-style', plugins_url('css/fm_db_manager.css', __FILE__));
        endif;
    }

    /*
    Menu Page - submenu
    */
    public function db_manager_menu_page()
    {
            $permissions = $this->permissions();
            if (empty($permissions)) {
                $permissions = 'manage_options';
            }
            add_submenu_page('wp_file_manager', 'DB Manager', 'DB Manager', $permissions, 'wp_file_manager_db_manager', array(&$this, 'wp_file_manager_db_manager'));
    }

    public function permissions()
        {
            $opt = get_option('wp_filemanager_options');
            $allowedroles = isset($opt['fm_user_roles_db']) ? $opt['fm_user_roles_db'] : '';
            if (empty($allowedroles)) {
                $allowedroles = array();
            }
            $current_user = wp_get_current_user();
            $userLogin = $current_user->user_login;
            $userID = $current_user->ID;
            $user = new WP_User($userID);
            if (!empty($user->roles) && is_array($user->roles)) {
                foreach ($user->roles as $role):
                    $role;
                endforeach;
            }
            $permissions = 'manage_options';
            $mk_count_u_roles = array_intersect($user->roles,$allowedroles);            
            if ($role == 'administrator'):
                $permissions = 'manage_options';
             elseif(count($mk_count_u_roles) > 0):
                $permissions = 'manage_options';
             endif;
            return $permissions;
        }

    /*
    DB table Main File
    */
    public function wp_file_manager_db_manager()
    {
        include 'inc/db_tables.php';
    }

    /*
    List Of Tables
    */
    public function show_tbls($dbtables)
    {
        ?>  
       <table width="100%" class="right_tbls">
        <tr>
        <th><?php _e('Tables', 'fm'); ?> (<?php echo count($dbtables); ?>) <a href="?page=wp_file_manager_db_manager&action=create_tbl" class="button"><?php _e('Create New Table'); ?></a></th>
        <th></th>
        </tr>
        <?php if (!empty($dbtables) && is_array($dbtables)):
        foreach ($dbtables as $key => $dbtable): ?>
        <tr>
        <td><a href="?page=wp_file_manager_db_manager&action=browse&tbl=<?php echo $dbtable; ?>"><?php echo $dbtable; ?></a></td>
        <td><a href="?page=wp_file_manager_db_manager&action=browse&tbl=<?php echo $dbtable; ?>" title="Browse: <?php echo $dbtable; ?>"><?php _e('Browse', 'fm'); ?></a> | <a href="?page=wp_file_manager_db_manager&action=insert&tbl=<?php echo $dbtable; ?>" title="Insert: <?php echo $dbtable; ?>"><?php _e('Insert', 'fm'); ?></a> | <a href="?page=wp_file_manager_db_manager&action=drop&tbl=<?php echo $dbtable; ?>" title="Delete Table: <?php echo $dbtable; ?>" onclick="return confirm('Are you sure want to delete Table? Deleted Table may not be recovered. So please delete carefully.')"><?php _e('Drop', 'fm'); ?></a> | <a href="?page=wp_file_manager_db_manager&action=truncate&tbl=<?php echo $dbtable; ?>" title="Empty Table: <?php echo $dbtable; ?>" onclick="return confirm('Are you sure want to empty Table? Table may not be recovered. So please delete carefully.')"><?php _e('Empty', 'fm'); ?></a></td>
        </tr>
        <?php endforeach;
        endif; ?>
        </table>
	<?php
    }

    /*
    * Browsing Records
    */
    public function BrowseRecords($table2Edit, $keys = '', $values = '', $offSet = 0, $eat_cols = 20, $fuzzy = '')
    {
        global $wpdb;
        // get the users data
        $keysArray = explode('~', $keys);
        $valsArray = explode('~', $values);
        //Get column information
        $cols = $wpdb->get_results('show columns from '.$table2Edit);

        //build where
        $where = '';
        $vals = array();
        for ($i = 0; $i < count($keysArray); ++$i) {
            //need to find out if the value is for a numeric field or not
            $isNumeric = 0;
            foreach ($cols as $col) {
                if ($col->Field == $keysArray[$i]) {
                    $isNumeric =
                        strpos($col->Type, 'int') !== false ||
                        strpos($col->Type, 'decimal') !== false ||
                        strpos($col->Type, 'float') !== false ||
                        strpos($col->Type, 'double') !== false ||
                        strpos($col->Type, 'real') !== false ||
                        strpos($col->Type, 'bit') !== false ||
                        strpos($col->Type, 'boolean') !== false ||
                        strpos($col->Type, 'serial') !== false;
                }
            }

            if ($keysArray[$i] != '') {
                if ($i != 0) {
                    $where = $where.' and ';
                }

                if ($isNumeric) {
                    $where = $where.$keysArray[$i].' = %d';
                    $vals[] = sanitize_text_field($valsArray[$i]);
                } else {
                    if ($fuzzy == 'checked') {
                        $where = $where.$keysArray[$i].' like %s';
                        $vals[] = sanitize_text_field('%'.$valsArray[$i].'%');
                    } else {
                        $where = $where.$keysArray[$i].' = %s';
                        $vals[] = sanitize_text_field($valsArray[$i]);
                    }
                }
            }
        }

        //Get the records
        if (count($vals) > 0) {
            $sql = $wpdb->prepare('select * from '.$table2Edit.' where '.$where.' LIMIT '.$offSet.', '.$eat_cols.'', $vals);
        } else {
            $sql = $wpdb->prepare('select * from '.$table2Edit.' LIMIT '.$offSet.', '.$eat_cols.'', null);
        }

        $records = stripslashes_deep($wpdb->get_results($sql, 'ARRAY_A'));
        $numCols = $wpdb->num_rows; ?>
		<div class="db_records">  
        <div class="btn_dv">
        <a href="?page=wp_file_manager_db_manager&amp;action=insert&amp;tbl=<?php echo $table2Edit; ?>" title="ADD: <?php echo $table2Edit; ?>" class="db_new_add"><?php _e('Add New', 'fm'); ?></a> <a href="?page=wp_file_manager_db_manager&action=drop&tbl=<?php echo $table2Edit; ?>" title="Delete Table: <?php echo $table2Edit; ?>" onclick="return confirm('Are you sure want to delete Table? Deleted Table may not be recovered. So please delete carefully.')" class="button dropBtn"><?php _e('Drop', 'fm'); ?></a> <?php if (count($records) > 0) {
            ?> <a href="?page=wp_file_manager_db_manager&action=truncate&tbl=<?php echo $table2Edit; ?>" title="Empty Table: <?php echo $table2Edit; ?>" onclick="return confirm('Are you sure want to empty Table? Table may not be recovered. So please delete carefully.')" class="button button-primary emptyBtn"><?php _e('Empty', 'fm'); ?></a> <?php
        } ?>
        </div>
		<?php
        if ($numCols > 0) {
            $primaryKeyExists = false; ?>
		   <table>
		   <tr>
           <th></th>
		   <?php foreach ($cols as $col) {
                ?>
		   <th><?php echo $col->Field; ?></th>
		   <?php
            } ?>
		   <th></th>
		   </tr>
		   <?php /* values */ ?>
			<?php 
            $in = 0;
            foreach ($records as $record) {
                ?>
			 <tr>
               <td class="action_btn">
								<?php
                                if (current_user_can('manage_options')) {
                                    ?>
                                 <a href="?page=wp_file_manager_db_manager&action=update&tbl=<?php echo $table2Edit; ?>&rid=<?php echo $records[$in][$cols[0]->Field]; ?>&pf=<?php echo $cols[0]->Field; ?>" onclick="return confirm('Are you sure want to update? It will redirect you to updation form.')" title="Edit"><img src="<?php echo plugins_url('/fm_dbmanager/images/edit-icon-png.png', dirname(__FILE__)); ?>" alt="Edit"/></a>
                                    <a href="?page=wp_file_manager_db_manager&action=delete&tbl=<?php echo $table2Edit; ?>&rid=<?php echo $records[$in][$cols[0]->Field]; ?>&pf=<?php echo $cols[0]->Field; ?>" onclick="return confirm('Are you sure want to delete? Deleted data may not be recovered. So please delete carefully.')" title="Delete"><img src="<?php echo plugins_url('/fm_dbmanager/images/delete-icon-png.png', dirname(__FILE__)); ?>" alt="Delete"/></a>
									<?php
                                } ?>
							</td>
			   <?php foreach ($cols as $col) {
                                    $row = $records[$in]; ?>
			   <?php if ($col->Key == 'PRI') {
                                        $primaryKeyExists = true; ?>
							<td class="primary_number" id="PRIMARY:<?php echo $col->Field; ?>"><?php echo $row[$col->Field]; ?></td>
							<?php
                                    } else {
                                        ?>
							<td id="<?php echo $col->Field; ?>"><input type="text"  value="<?php echo esc_html($row[$col->Field]); ?>" readonly/></td>
							<?php
                                    } ?>
			   <?php
                                } ?>
			  <td class="action_btn">
								<?php
                                if (current_user_can('manage_options')) {
                                    ?>
                                   <a href="?page=wp_file_manager_db_manager&action=update&tbl=<?php echo $table2Edit; ?>&rid=<?php echo $records[$in][$cols[0]->Field]; ?>&pf=<?php echo $cols[0]->Field; ?>" onclick="return confirm('Are you sure want to update? It will redirect you to updation form.')" title="Edit"><img src="<?php echo plugins_url('/fm_dbmanager/images/edit-icon-png.png', dirname(__FILE__)); ?>" alt="Edit"/></a>
                                    <a href="?page=wp_file_manager_db_manager&action=delete&tbl=<?php echo $table2Edit; ?>&rid=<?php echo $records[$in][$cols[0]->Field]; ?>&pf=<?php echo $cols[0]->Field; ?>" onclick="return confirm('Are you sure want to delete? Deleted data may not be recovered. So please delete carefully.')" title="Delete"><img src="<?php echo plugins_url('/fm_dbmanager/images/delete-icon-png.png', dirname(__FILE__)); ?>" alt="Delete"/></a>
									<?php
                                } ?>
							</td>
			 </tr> 
			<?php ++$in;
            } ?>
		   </table>
			<?php
        } else {
            $this->error('No Records Found!');
        } ?>
				</div>
		<?php
    }

    /* Table Table */
    public function TableDetails($table2Edit)
    {
        global $wpdb;
        $cols = $wpdb->get_results('show columns from '.$table2Edit); ?>
		<form action="" method="post">
		<div class="add_form_dv">
			<table id="tableCols">
				<tr>
					<th><strong><?php _e('Column', 'ffm'); ?></strong></th>
					<th><strong><?php _e('Value', 'ffm'); ?></strong></th>
				</tr>
			<?php
                foreach ($cols as $col) {
                    ?>
					<tr>
						<td>
							<?php
                                echo $col->Field.' ('.$col->Type.')';
                    if ($col->Key == 'PRI') {
                        echo ' [PRIMARY]';
                    } ?>
	
						</td>
						<td>
							<input type="text" id="<?php echo sanitize_text_field($col->Field); ?>" name="<?php echo sanitize_text_field($col->Field); ?>" value="" />
						</td>
	
					</tr>
				<?php
                } ?>
			</table>
		</div>
			
		<div class="bot_btn_dv">
				&nbsp;
			<input type="reset" class="reset-button" value="Reset" name="reset" />
			<?php
            // Check that editor has rights to add
            if (current_user_can('manage_options')) {
                ?>
			&nbsp;
			<input type="submit" class="add-button" value="Add" name="save_tbl_data" />
			<?php
            } ?>
		</div>
		
		</form>
		<?php
    }

    /* Create Record */
    public function CreateRecord($table2Edit, $values)
    {
        global $wpdb;
        $continue = false;
        if (!empty($values)) {
            foreach ($values as $key => $val) {
                if (!empty($values[$key])) {
                    $continue = true;
                }
            }
        }
        if ($continue) {
            if ($wpdb->insert($table2Edit, $values)) {
                $this->success('New Record Created');
            } else {
                $this->error('Unable to create new record');
                $wpdb->show_errors();
                $wpdb->print_error();
                $wpdb->hide_errors();
            }
        } else {
            $this->error('Unable to create new record');
        }
    }

    /* Update Table Details */
    public function UpdateTableDetails($table2Edit, $fieldname, $rid)
    {
        if (empty($table2Edit) || empty($fieldname) || empty($rid)) {
            $this->error('Oops! Some thing went wrong! Please try again.');
        } else {
            global $wpdb;
            $cols = $wpdb->get_results('show columns from '.$table2Edit);
            $sql = 'select * from '.$table2Edit.' where '.$fieldname." = '".$rid."'";
            $record = stripslashes_deep($wpdb->get_row($sql, 'ARRAY_A')); ?>
        <div class="back_btn_dv">
        <a href="?page=wp_file_manager_db_manager&action=browse&tbl=<?php echo $table2Edit; ?>"><?php _e('&larr; Back', 'fm'); ?></a>
      </div>
		<form method="post" id="update_tbl_form">
		<div style="overflow: auto">
			<table id="tableCols">
				<tr>
					<th><strong><?php _e('Column', 'ffm'); ?></strong></th>
					<th><strong><?php _e('Value', 'ffm'); ?></strong></th>
				</tr>
			<?php
                foreach ($cols as $col) {
                    ?>
					<tr>
						<td>
							<?php
                                echo $col->Field.' ('.$col->Type.')';
                    if ($col->Key == 'PRI') {
                        echo ' [PRIMARY]';
                    } ?>
	
						</td>
						<td>
							<input type="text" name="<?php echo sanitize_text_field($col->Field); ?>" value="<?php echo $record[$col->Field]; ?>" />
						</td>
	
					</tr>
				<?php
                } ?>
			</table>
		</div>
			
		<div class="bot_btn_dv">
			<?php
            // Check that editor has rights to add
            if (current_user_can('manage_options')) {
                ?>
			&nbsp;
			<input type="submit" class="custom_btn update_tbl_data" value="Update" name="update_tbl_data" />
			<?php
            } ?>
		</div>
		
		</form>
		<?php
        }
    }

    /* Update Record - Ajax*/
    public function UpdateTblRecord()
    {
        global $wpdb;
        $table2Edit = $_POST['table2Edit'];
        $fieldname = $_POST['fieldname'];
        $rid = intval($_POST['rid']);
        $value = array();
        parse_str($_POST['values'], $value);
        $tbl_data = json_encode($value);
        $decoded_tbl_data = json_decode($tbl_data, true);
        unset($decoded_tbl_data['update_tbl_data']);
        $continue = false;
        if (!empty($decoded_tbl_data[$fieldname])) {
            $continue = true;
        }
        if ($continue) {
            if ($wpdb->update($table2Edit, $decoded_tbl_data, array($fieldname => $rid))) {
                echo json_encode(array('type' => 'success', 'msg' => 'Record <strong>'.$table2Edit.' : '.$rid.'</strong> Updated Successfully'));
            } else {
                echo json_encode(array('type' => 'error', 'msg' => 'Unable to Update record'));
            }
        } else {
            echo json_encode(array('type' => 'error', 'msg' => 'Unable to Update record'));
        }
        die;
    }

    /* Delete Record */
    public function deleteRecord($table2Edit, $fieldname, $rid)
    {
        if (empty($table2Edit) || empty($fieldname) || empty($rid)) {
            $this->error('Oops! Some thing went wrong! Please try again.');
        } else {
            ?> 
          <a href="?page=wp_file_manager_db_manager&action=browse&tbl=<?php echo $table2Edit; ?>" class="button"><?php _e('&larr; Back', 'fm'); ?></a>
		 <?php global $wpdb;
            $deleteRow = $wpdb->delete($table2Edit, array($fieldname => $rid));
            if ($deleteRow) {
                $this->success('Record <strong>'.$table2Edit.' :: '.$fieldname.' :: '.$rid.' Deleted successfully.</strong>');
            } else {
                $this->error('Error occured while deleting a record.');
            }
        }
    }

    public function createTable($POST)
    {
        global $wpdb;
        $table_name = $_POST['table'];
        unset($POST['table']);
        $charset_collate = $wpdb->get_charset_collate();
        $error = array();
        if (count($POST['field_name']) > 0) {
            $sql = "CREATE TABLE $table_name (";
            foreach ($POST['field_name'] as $key => $val):
            if ($POST['field_type'][$key] == 'INT') {
                $fieldlength = '11';
            } else {
                $fieldlength = $POST['field_length'][$key];
            }
            $emptyCheck = $POST['field_null'][$key];
            if (empty($POST['field_null'][$key])) {
                $emptyCheck = 'NOT NULL';
            }
            if (empty($POST['field_extra'][0])) {
                $POST['field_extra'][0] = 'AUTO_INCREMENT';
            }
            $sql .= ''.$val.' '.$POST['field_type'][$key].'('.$fieldlength.') '.$emptyCheck.' '.$POST['field_extra'][$key].',';
            endforeach;
            $sql .= 'PRIMARY KEY  ('.$POST['field_name'][0].')';
            $sql .= ") $charset_collate;";
        }
        $create_TBL = $wpdb->query($sql);
        if ($create_TBL) {
            $this->success('Table Created Successfully !');
        } else {
            $this->error('Table Not Created !');
        }
    }

    /* Create new Table */
    public function create_tbl()
    {
        include 'inc/createTbl.php';
    }

    /* drop tbl */
    public function drop_table($tableName)
    {
        global $wpdb;
        $dropTbl = $wpdb->query('DROP TABLE '.$tableName.'');
        if ($dropTbl) {
            $this->success('Table <strong>'.$tableName.'</strong> Deleted Successfully!');
        } else {
            $this->error('Table <strong>'.$tableName.'</strong> Not Deleted!');
        }
    }

    /* Truncate tbl */
    public function truncate_tbl($tableName)
    {
        global $wpdb;
        $truncateTbl = $wpdb->query('TRUNCATE TABLE '.$tableName.'');
        if ($truncateTbl) {
            $this->success('Table records of <strong>'.$tableName.'</strong> Deleted Successfully!');
        } else {
            $this->error('Table records of <strong>'.$tableName.'</strong> Not Deleted!');
        }
    }

    /* Export DB page */
    public function export_db()
    {
        include 'inc/exportdb.php';
    }

    /* backup */
    public function exportDB($host, $user, $pass, $name, $tables = false, $backup_name = false)
    {
        $mysqli = new mysqli($host, $user, $pass, $name);
        $mysqli->select_db($name);
        $mysqli->query("SET NAMES 'utf8'");
        $queryTables = $mysqli->query('SHOW TABLES');
        while ($row = $queryTables->fetch_row()) {
            $target_tables[] = $row[0];
        }
        if ($tables !== false) {
            $target_tables = array_intersect($target_tables, $tables);
        }
        foreach ($target_tables as $table) {
            $result = $mysqli->query('SELECT * FROM '.$table);
            $fields_amount = $result->field_count;
            $rows_num = $mysqli->affected_rows;
            $res = $mysqli->query('SHOW CREATE TABLE '.$table);
            $TableMLine = $res->fetch_row();
            $content = (!isset($content) ? '' : $content)."\n\n".$TableMLine[1].";\n\n";
            for ($i = 0, $st_counter = 0; $i < $fields_amount; $i++, $st_counter = 0) {
                while ($row = $result->fetch_row()) {
                    if ($st_counter % 100 == 0 || $st_counter == 0) {
                        $content .= "\nINSERT INTO ".$table.' VALUES';
                    }
                    $content .= "\n(";
                    for ($j = 0; $j < $fields_amount; ++$j) {
                        $row[$j] = str_replace("\n", '\\n', addslashes($row[$j]));
                        if (isset($row[$j])) {
                            $content .= '"'.$row[$j].'"';
                        } else {
                            $content .= '""';
                        }
                        if ($j < ($fields_amount - 1)) {
                            $content .= ',';
                        }
                    }
                    $content .= ')';

                    if ((($st_counter + 1) % 100 == 0 && $st_counter != 0) || $st_counter + 1 == $rows_num) {
                        $content .= ';';
                    } else {
                        $content .= ',';
                    }
                    $st_counter = $st_counter + 1;
                }
            }
            $content .= "\n\n\n";
        }
        $backup_name = $backup_name ? $backup_name : $name.'_('.date('H-i-s').'_'.date('d-m-Y').')__'.rand(1, 11111111).'.sql';
        header('Content-Type: application/octet-stream');
        header('Content-Transfer-Encoding: Binary');
        header('Content-disposition: attachment; filename="'.$backup_name.'"');
        echo $content;
        exit;
    }

    /* Error Msg */
    public function error($msg)
    {
        _e('<div id="setting-error-settings_updated" class="error settings-error notice"><p><strong>'.$msg.'</strong></p></div>', 'fm');
    }

    /* Success Msg */
    public function success($msg)
    {
        _e('<div id="setting-error-settings_updated" class="updated settings-error notice"><p><strong>'.$msg.'</strong></p></div>', 'fm');
    }

    /* Alert */
    public function alert($msg)
    {
        $alert = '<script>';
        $alert .= 'alert("'.$msg.'")';
        $alert .= '</script>';

        return $alert;
    }
}
endif;
/* Required to hook with WP File Manager */
add_action('load_filemanager_extensions', 'dbmanager_extension_load');
function dbmanager_extension_load()
{
    new mk_db_manager();
}
