<?php
include 'config.php';
$demodb = "pumpmanager_raw_db_structure";
// $demodb2="imconnect_pump_demo_18"; /*24HR*/
// $demodb3="imconnect_pump_demo_18"; /*CREDIT*/
$password_for_data_flush = 'Pumpmanager@2787';
function copyDBWithData($olddb, $db_name1, $DB_HOST, $DB_USER, $DB_PASS)
{
    ($mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS)) or die($mysqli->error);

    ($database = $mysqli->query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='$db_name1'")) or die($mysqli->error);
    if ($database->num_rows == 0) {
        $response['code'] = 1; // Error
        $response['message'] = $db_name1 . ' - DATABASE NOT CREATED';
        echo json_encode($response);
        exit();
    }

    ($tables = $mysqli->query("SHOW TABLES FROM $olddb")) or die($mysqli->error);
    while ($table = $tables->fetch_array()) {
        $TABLE = $table[0];
        $mysqli->query("CREATE TABLE $db_name1.$TABLE LIKE $olddb.$TABLE") or die($mysqli->error);
        $mysqli->query("INSERT INTO $db_name1.$TABLE SELECT * FROM $olddb.$TABLE") or die($mysqli->error);
    }
}

function createDB($DB_HOST, $DB_USER, $DB_PASS, $cpanelUsername, $cpanelPassword, $databaseName, $mainDatabase)
{
    global $databaseUser;
    global $databaseUser2;
    $phpMyAdminUrl = "https://pumpmanager.in:2083/cpsess1161737911/frontend/jupiter/sql/addb.html";

    $loginData = [
        'pma_username' => $cpanelUsername,
        'pma_password' => $cpanelPassword,
    ];

    $curlOptions = [
        CURLOPT_URL => $phpMyAdminUrl,
        CURLOPT_POST => true,
        CURLOPT_POSTFIELDS => http_build_query($loginData),
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_USERPWD => "$cpanelUsername:$cpanelPassword",
    ];

    $ch = curl_init();
    curl_setopt_array($ch, $curlOptions);

    $response = curl_exec($ch);

    if (strpos($response, 'phpMyAdmin') !== false) {
        $createDatabaseData = ['db' => $databaseName];

        $createDatabaseOptions = [
            CURLOPT_URL => $phpMyAdminUrl,
            CURLOPT_POST => true,
            CURLOPT_POSTFIELDS => http_build_query($createDatabaseData),
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_FOLLOWLOCATION => true,
        ];

        curl_setopt_array($ch, $createDatabaseOptions);
        $createDatabaseResponse = curl_exec($ch);
        // var_dump($createDatabaseResponse);
        if (strpos($createDatabaseResponse, 'Added the database') !== false) {
            // echo "Database created successfully.";

            // ... (Code to add user privileges to the database)
            $lastUrl = curl_getinfo($ch, CURLINFO_EFFECTIVE_URL);
            //$db_name1=$user.'_'.$newdb;
            $myvars = 'user=' . $databaseUser . '&db=' . $databaseName . '&privileges=ALL';
            $url1 = "https://pumpmanager.in:2083/cpsess1161737911/frontend/jupiter/sql/addusertodb.html";
            $ch = curl_init($url1);
            curl_setopt($ch, CURLOPT_POST, 1);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $myvars);
            curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
            curl_setopt($ch, CURLOPT_HEADER, 0);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
            curl_setopt($ch, CURLOPT_USERPWD, "$cpanelUsername:$cpanelPassword");
            $lastUrl1 = curl_getinfo($ch, CURLINFO_EFFECTIVE_URL);
            $response1 = curl_exec($ch);
            if (curl_exec($ch) === false) {
                $response['code'] = 500; // Error
                $response['message'] = 'Curl error: ' . curl_error($ch);
                exit();
            } else {
                // $response['code'] = 200; // Error
                // $response['message'] = 'Application user added to database';
            }

            $lastUrl = curl_getinfo($ch, CURLINFO_EFFECTIVE_URL);
            //$db_name1=$user.'_'.$newdb;
            $myvars = 'user=' . $databaseUser2 . '&db=' . $databaseName . '&privileges=ALL';
            $url1 = "https://pumpmanager.in:2083/cpsess1161737911/frontend/jupiter/sql/addusertodb.html";
            $ch = curl_init($url1);
            curl_setopt($ch, CURLOPT_POST, 1);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $myvars);
            curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
            curl_setopt($ch, CURLOPT_HEADER, 0);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
            curl_setopt($ch, CURLOPT_USERPWD, "$cpanelUsername:$cpanelPassword");
            $lastUrl1 = curl_getinfo($ch, CURLINFO_EFFECTIVE_URL);
            $response1 = curl_exec($ch);

            if (curl_exec($ch) === false) {
                $response['code'] = 500; // Error
                $response['message'] = 'Curl error: ' . curl_error($ch);
                exit();
            } else {
                // $response['code'] = 200; // Error
                // $response['message'] = 'Desktop user added to database';
            }
            curl_close($ch);

            copyDBWithData($mainDatabase, $databaseName, $DB_HOST, $DB_USER, $DB_PASS);
            
        } else {
            $response['code'] = 1; // Error
            $response['message'] = "Failed to create the database.";
            echo json_encode($response);
        }
    } else {
        $response['code'] = 1; // Error
        $response['message'] = "Login failed.";
        echo json_encode($response);
    }

    curl_close($ch);
}

function fetchclients3()
{
    include 'config.php';
    $querymain = "SELECT * FROM `tbl_login` WHERE status='Active'";
    $resultmain = $con->query($querymain);
    while ($datamain = $resultmain->fetch_assoc()) {
        $cname[] = $datamain['Usr_Name'];
    }

    return $cname;
}
$cmpname3 = fetchclients3();
function fetchclients()
{
    include 'config.php';
    $querymain = "SELECT * FROM `tbl_login` ";
    $resultmain = $con->query($querymain);
    while ($datamain = $resultmain->fetch_assoc()) {
        $cname[] = $datamain['Usr_db'];
    }
    return $cname;
}
$cmpname = fetchclients();

if (!empty($_POST)) {
    
    if ($_POST["request_type"] == 5) {
        $output = '';
        $message = '';
        $cuname = mysqli_real_escape_string($con, $_POST["cuname"]);
        $dbname = mysqli_real_escape_string($con, $_POST["dbname"]);
        $c_name = mysqli_real_escape_string($con, $_POST["c_name"]);
        $cpass = mysqli_real_escape_string($con, $_POST["cpass"]);
        $cpass2 = mysqli_real_escape_string($con, $_POST["cpass2"]);
        $ccontact = mysqli_real_escape_string($con, $_POST["ccontact"]);
        $c_address = mysqli_real_escape_string($con, $_POST["c_address"]);
        $date = mysqli_real_escape_string($con, $_POST["reg_date"]);
        $exp_date = mysqli_real_escape_string($con, $_POST["exp_date"]);
        $status = mysqli_real_escape_string($con, $_POST["status"]);
        $brand = mysqli_real_escape_string($con, $_POST["brand"]);
        $gst = mysqli_real_escape_string($con, $_POST["gst"]);
        $vat = mysqli_real_escape_string($con, $_POST["vat"]);
        $Usr_email = mysqli_real_escape_string($con, $_POST["Usr_email"]);
        $oldPath = mysqli_real_escape_string($con, $_POST["old_path"]);
        // $olddb='redatsin_demopump';
        $olddb = $demodb;
        $newdb = "pumpmanager_" . $cuname;
        // $newdb1 ="imconnect_pump_".$cuname;
        $newdb1 = "pumpmanager_" . $cuname;
        
        if (is_uploaded_file($_FILES["company_logo"]["tmp_name"])) {
            //echo 'if';
            $start_temp_file = $_FILES["company_logo"]["tmp_name"];
            $start_file = $_FILES["company_logo"]["name"];
        
            $target_file = "../public/assets/company_img/" . $start_file;
            $imageFileType = strtolower(pathinfo($target_file, PATHINFO_EXTENSION));
        
            $start_img_name = $dbname . "_logo." . $imageFileType;
            $start_upload_dir = "../public/assets/company_img/" . $start_img_name;
        
            if (move_uploaded_file($start_temp_file, $start_upload_dir)) {
                $imagePath = $start_img_name;
            } else {
                $imagePath = $oldPath;
            }
        } else {
            //echo 'else';
            $imagePath = $oldPath;
        }
        
        if ($_POST["employee_id"] != '') {
            $query = "
                    UPDATE tbl_login 
                    SET 
                        cName='$c_name',
                        password2 = '$cpass2',
                        Usr_Phone='$ccontact',
                        Usr_Address='$c_address',
                        date='$date',
                        expDate = '$exp_date',
                        status='$status',
                        brand = '$brand',
                        Usr_db = '$dbname',
                        Usr_Gst = '$gst',
                        Usr_Vat = '$vat',
                        Usr_email = '$Usr_email',
                        company_logo = '$imagePath' 
                    WHERE 
                        Usr_id='" . $_POST["employee_id"] ."'";
            mysqli_query($con, $query) or die($con->error);
            
            
        } else {
            $query = "INSERT INTO `tbl_login`
                        (`Usr_Name`, `Usr_db`, `cName`, `Password`, `password2`, `Usr_Phone`, `Usr_Address`, `date`, `expDate`, `status`, `brand`,`Usr_Gst`,`Usr_Vat`,`Usr_email`,`company_logo`,`clienthostname`) 
                      VALUES 
                        ('$cuname','$newdb1','$c_name','$cpass','$cpass2','$ccontact','$c_address','$date','$exp_date','Active','$brand','$gst','$vat','$Usr_email','$imagePath','$cuname')";
            mysqli_query($con, $query) or die($con->error);
            //================================ create database ================================================
            $cpanelUsername = 'pumpmanager';
            $cpanelPassword = 'Moresh@2787';
            $databaseUser = "pumpmanager_test_user"; // database user 1
            $databasePassword = "Moresh@2787";

            $DB_HOST = 'localhost';
            $DB_USER = $databaseUser;
            $DB_PASS = $databasePassword;
            $database_new = $newdb;
            $database_old = 'pumpmanager_raw_db_structure';

            createDB($DB_HOST, $DB_USER, $DB_PASS, $cpanelUsername, $cpanelPassword, $database_new, $database_old);
            
            $conClient = new mysqli($DB_HOST, $DB_USER, $DB_PASS,$database_new);
            
            if ($conn->connect_error) {
                throw new Exception("Connection failed: " . $conn->connect_error);
            }
    
            // Insert data into the new database's tables
            $sqlClient = "INSERT INTO `users`(`name`, `password`, `email`) VALUES ('$cuname','$cpass','$email')";
            $resultCheck = mysqli_query($conClient, $sqlClient);
        }

        $get_all_events = mysqli_query($con, "SELECT * FROM tbl_login");
        $get_all_events_num = mysqli_num_rows($get_all_events);
        if ($get_all_events_num > 0) {
            while ($sp_events = mysqli_fetch_assoc($get_all_events)) {
                $date = date("Y-m-d");
                $id = $sp_events['Usr_id'];
                $expire_date = $sp_events['expDate'];
                $update_events = mysqli_query($con, "UPDATE tbl_login SET status='Deactive' WHERE DATE(expDate) < '$date' AND Usr_id='$id'");
                if ($update_events) {
                    //   echo "changed";
                    //   echo "<br>";
                } else {
                    //   echo "no changed";
                    //   echo "<br>";
                }
            }
        }
        echo $output;
    }
    if ($_POST["request_type"] == 6) {
        $query = "SELECT * FROM tbl_login WHERE Usr_id = '" . $_POST["id"] . "'";
        $result = mysqli_query($con, $query);
        $row = mysqli_fetch_array($result);

        $query = "DELETE FROM tbl_login WHERE Usr_id = '" . $_POST["id"] . "'";
        mysqli_query($con, $query);
    }
    if ($_POST["request_type"] == 7) {
        //   echo $_POST["id"];
        // if(strlen($_POST["id"])>3){
        $result2 = mysqli_query($con, "select * from `tbl_login` where Usr_db='" . $_POST["id"] . "'");
        $data2 = $result2->fetch_object();
        if (strlen($data2->id) > 0) {
            echo 'no';
        } else {
            echo 'ok';
        }
        // }else{
        //   echo 'lessthan3';
        // }
    }
    if ($_POST["request_type"] == 8) {
        if (isset($_POST["employee_id"])) {
            $query = "SELECT * FROM tbl_login WHERE Usr_id = '" . $_POST["employee_id"] . "'";
            $result = mysqli_query($con, $query);
            $row = mysqli_fetch_array($result);
            echo json_encode($row);
        }
    }
    if ($_POST["request_type"] == 9) {

        $sql = "SELECT * FROM tbl_login ORDER BY Usr_id DESC";
        $result = mysqli_query($con, $sql);
        $id = 1;
        ?>
          <table class="table table-bordered">  
            <tr class="danger">  
              <th>Sr. No</th>
              <th width="15%">Client Username</th>
              <th width="15%">Company Name</th>
              <th>DB Name</th>
              <th>Expire Date</th>
              <th>Validity</th>
              <th>Brand</th>
              <th>Logo</th>
              <th>Status</th>
              <th>Login</th>
              <th width="10%">Action</th>   
            </tr>  
            <?php while ($row = mysqli_fetch_array($result)) { ?>  
              <tr> 
                <td><?php echo $id++; ?></td> 
                <td><?php echo $row["Usr_Name"]; ?></td>
                <td><?php echo $row["cName"]; ?></td>
                <td><?php echo $row["Usr_db"]; ?></td>
                <td><?php echo $row["expDate"]; ?></td>
                <td><?php
                $cdate = date('Y-m-d');
                $start_date = strtotime($cdate);
                $end_date = strtotime($row["expDate"]);
                $vali = ($end_date - $start_date) / 60 / 60 / 24;
                if ($vali > 0) {
                    echo $vali . ' DAYS';
                }
                ?></td>
                <td><?php echo $row["brand"]; ?></td>
                <td><?php if($row["company_logo"]){ echo '<img src="../public/assets/company_img/'.$row["company_logo"].'" style="width:80px">'; }else{ echo 'NA'; } ?></td>
                <td><?php echo $row["status"]; ?></td>
                <td><?php if ($vali > 0) { ?><a class='login1' id="<?php echo $row["Usr_Name"]; ?>" target="_blank"><input type="button" value="Login" name="<?php echo $row["Usr_Name"]; ?>" class="btn btn-danger btn-xs login" /></a> <?php } ?></td>
                <td>
                  <input type="button" name="edit" value="Edit" id="<?php echo $row["Usr_id"]; ?>" class="btn btn-success btn-xs edit_data" />&nbsp;&nbsp;
                  <input type="button" name="view" value="View" id="<?php echo $row["Usr_id"]; ?>" class="btn btn-info btn-xs view_data" />
                </td>  
              </tr>  
              <?php } ?>  
          </table>
  <?php  }
  
    if ($_POST["request_type"] == 10) {
        if (isset($_POST["employee_id"])) {
            $output = '';
            //$con = mysqli_con("localhost", "root", "", "imcon_qisystems");
            $query = "SELECT * FROM tbl_login WHERE Usr_id = '" . $_POST["employee_id"] . "'";
            $result = mysqli_query($con, $query);
            $output .= '  
                <div class="table-responsive">  
                <table class="table table-bordered">';
                    while ($row = mysqli_fetch_array($result)) {
                        $output .=
                        '<tr>  
                            <td width="30%"><label>Client Username</label></td>  
                            <td width="70%">' .$row["Usr_Name"] .'</td>  
                        </tr>  
                        <tr>  
                            <td width="30%"><label>Client Database</label></td>  
                            <td width="70%">' .$row["Usr_db"] .'</td>  
                        </tr>  
                        <tr>  
                            <td width="30%"><label>Company Name</label></td>  
                            <td width="70%">' .$row["cName"] .'</td>  
                        </tr>  
                        <tr>  
                            <td width="30%"><label>Brand</label></td>  
                            <td width="70%">' . $row["brand"] .'</td>  
                        </tr>  
                        <tr>  
                            <td width="30%"><label>Password</label></td>  
                            <td width="70%">' . $row["Password"] .'</td>  
                        </tr>  
                        <tr>  
                            <td width="30%"><label>Password 2</label></td>  
                            <td width="70%">' . $row["password2"] .'</td>  
                        </tr> 
                        <tr>  
                            <td width="30%"><label>GST</label></td>  
                            <td width="70%">' .$row["Usr_Gst"] .'</td>  
                        </tr>  
                        <tr>  
                            <td width="30%"><label>VAT</label></td>  
                            <td width="70%">' .$row["Usr_Vat"] .'</td>  
                         </tr> 
                        <tr>  
                            <td width="30%"><label>Contact Number</label></td>  
                            <td width="70%">' .$row["Usr_Phone"] .'</td>  
                        </tr> 
                        <tr>  
                            <td width="30%"><label>Client Address</label></td>  
                            <td width="70%">' .$row["Usr_Address"] .'</td>  
                        </tr> 
                        <tr>  
                            <td width="30%"><label>Reg Date</label></td>  
                            <td width="70%">' .$row["date"] .'</td>  
                        </tr> 
                        <tr>  
                            <td width="30%"><label>Expire Date</label></td>  
                            <td width="70%">' .$row["expDate"] .'</td>  
                        </tr> 
                        <tr>  
                            <td width="30%"><label>Status</label></td>  
                            <td width="70%">' .$row["status"] .'</td>  
                        </tr>';
                    }
            $output .= ' </table> </div>';
            echo $output;
        }
    }

    if ($_POST["request_type"] == 21) {
        $table_name = $_POST['table_name'];
        //print_r($cmpname);
        if (isset($table_name)) {
            foreach ($cmpname as $dbname122tk) {
                $dbname122tk;
                $concronsk = mysqli_connect($host, $user, $password, $dbname122tk);
                //var_dump($concronsk);
                $query = "CREATE TABLE $table_name(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT)";
                $result = $concronsk->query($query);
            }
        }
    }
    if ($_POST["request_type"] == 22) {
        $table_name = $_POST['table_name'];
        $column_name = $_POST['column_name'];
        $column_type = $_POST['column_type'];
        if (isset($table_name) && isset($column_name) && isset($column_type)) {
            foreach ($cmpname as $dbname122tk) {
                if ($column_type == 'VARCHAR' || $column_type == 'INT') {
                    $column = $column_name . ' ' . $column_type . '(255)';
                } else {
                    $column = $column_name . ' ' . $column_type;
                }
                $concronsk = mysqli_connect($host, $user, $password, $dbname122tk);
                $query = "ALTER TABLE $table_name ADD $column  NOT NULL";
                $result = $concronsk->query($query);
            }
        }
    }
    if ($_POST["request_type"] == 23) { ?>
        <table class="table table-bordered" >  
            <?php 
            foreach ($cmpname as $dbname122tk) {
                $concronsk = mysqli_connect($host, $user, $password, $dbname122tk);
                $query = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='" . $dbname122tk . "'  AND TABLE_NAME not in ('tbl_oilpouchsale','tbl_oilpouch') GROUP BY TABLE_NAME";
                $result = $concronsk->query($query);
                if ($dbname122tk == $demodb) {
                    $sdsd = 0;
                    $query11 = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='" . $dbname122tk . "'  AND TABLE_NAME not in ('tbl_oilpouchsale','tbl_oilpouch') GROUP BY TABLE_NAME";
                    $result11 = $concronsk->query($query11);
                    while ($data11 = $result11->fetch_assoc()) {
                        $sdsd++;
                    }
                    echo "<tr class='danger'> <th>DB Name </th><th colspan='" . $sdsd . "'>Table Name </th></tr>";
                }
                echo " <tr class='info'> <td class='danger'>" . $dbname122tk . " </td>";
                while ($data = $result->fetch_assoc()) {
                    $query1 =
                        "select count(*) as sksk from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='" .
                        $dbname122tk .
                        "' AND TABLE_NAME='" .
                        $data['TABLE_NAME'] .
                        "'  AND TABLE_NAME not in ('tbl_oilpouchsale','tbl_oilpouch')  GROUP BY TABLE_NAME";
                    $result1 = $concronsk->query($query1);
                    $data1 = $result1->fetch_assoc();
                    echo "<td>" . $data['TABLE_NAME'] . "(" . $data1['sksk'] . ")</td>";
                    $count++;
                }
                echo "</tr>";
            } ?>
        </table>
  <?php }

    if ($_POST["request_type"] == 24) { ?>
        <form method="post" id="addColumn"> 
            Table : <select class="dbname" name="table_name">
            <?php
                $concronsk = mysqli_connect($host, $user, $password, $demodb);
                $query21 = "select * from INFORMATION_SCHEMA.COLUMNS  where TABLE_SCHEMA='$demodb'  GROUP BY TABLE_NAME";
                $result21 = $concronsk->query($query21);
                while ($data21 = $result21->fetch_assoc()) {
                    echo "<option>" . $data21['TABLE_NAME'] . "</option>";
                }
            ?>
            </select>
            <select>
                Column Name : <input type="text" name="column_name">
                Column Type<select name="column_type">
                  <option value="VARCHAR">VARCHAR</option>
                  <option value="INT">INT</option>
                  <option value="DATE">DATE</option>
                  <option value="DATETIME">DATETIME</option>
            </select>
            <input type="hidden" name="request_type" id="request_type" readonly value="22" />  
            <button type="button" class="addColumn btn btn-primary btn-md" onclick="addColumn()">ADD</button>
        </form>
    <?php }
    
    if ($_POST["request_type"] == 25) {
        $table_name = $_POST['table_name'];
        if (isset($table_name)) {
            foreach ($cmpname2 as $dbname122tk) {
                $concronsk = mysqli_connect($host, $user, $password, $dbname122tk);
                $query = "CREATE TABLE $table_name(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT)";
                $result = $concronsk->query($query);
            }
        }
    }
    if ($_POST["request_type"] == 26) {
        $table_name = $_POST['table_name'];
        $column_name = $_POST['column_name'];
        $column_type = $_POST['column_type'];
        if (isset($table_name) && isset($column_name) && isset($column_type)) {
            foreach ($cmpname2 as $dbname122tk) {
                if ($column_type == 'VARCHAR' || $column_type == 'INT') {
                    $column = $column_name . ' ' . $column_type . '(255)';
                } else {
                    $column = $column_name . ' ' . $column_type;
                }
                $concronsk = mysqli_connect($host, $user, $password, $dbname122tk);
                $query = "ALTER TABLE $table_name ADD $column  NOT NULL";
                $result = $concronsk->query($query);
            }
        }
    }
    if ($_POST["request_type"] == 27) { ?>
        <table class="table table-bordered" >  
          <?php foreach ($cmpname2 as $dbname122tk) {
              $concronsk = mysqli_connect($host, $user, $password, $dbname122tk);
              $query = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='" . $dbname122tk . "' AND TABLE_NAME not in ('tbl_oilpouchsale','tbl_oilpouch') GROUP BY TABLE_NAME";
              $result = $concronsk->query($query);
              if ($dbname122tk == $demodb2) {
                  $sdsd = 0;
                  $query11 = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='" . $dbname122tk . "' AND TABLE_NAME not in ('tbl_oilpouchsale','tbl_oilpouch') GROUP BY TABLE_NAME";
                  $result11 = $concronsk->query($query11);
                  while ($data11 = $result11->fetch_assoc()) {
                      $sdsd++;
                  }
                  echo "<tr class='danger'> <th>DB Name </th><th colspan='" . $sdsd . "'>Table Name </th></tr>";
              }
              echo " <tr class='info'> <td class='danger'>" . $dbname122tk . " </td>";
              while ($data = $result->fetch_assoc()) {
                  $query1 =
                      "select count(*) as sksk from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='" .
                      $dbname122tk .
                      "' AND TABLE_NAME='" .
                      $data['TABLE_NAME'] .
                      "' AND TABLE_NAME not in ('tbl_oilpouchsale','tbl_oilpouch') GROUP BY TABLE_NAME";
                  $result1 = $concronsk->query($query1);
                  $data1 = $result1->fetch_assoc();
                  echo "<td>" . $data['TABLE_NAME'] . "(" . $data1['sksk'] . ")</td>";
                  $count++;
              }
              echo "</tr>";
          } ?>
        </table>
    <?php }
    if ($_POST["request_type"] == 28) { ?>
        <form method="post" id="addColumn"> 
            Table : <select class="dbname" name="table_name">
            <?php
                $concronsk = mysqli_connect($host, $user, $password, $demodb2);
                $query21 = "select * from INFORMATION_SCHEMA.COLUMNS  where TABLE_SCHEMA='$demodb2'  GROUP BY TABLE_NAME";
                $result21 = $concronsk->query($query21);
                while ($data21 = $result21->fetch_assoc()) {
                    echo "<option>" . $data21['TABLE_NAME'] . "</option>";
                }
            ?>
            </select>
            <select>
                Column Name : <input type="text" name="column_name">
                Column Type<select name="column_type">
                <option value="VARCHAR">VARCHAR</option>
                <option value="INT">INT</option>
                <option value="DATE">DATE</option>
                <option value="DATETIME">DATETIME</option>
            </select>
            <input type="hidden" name="request_type" id="request_type" readonly value="26" />  
            <button type="button" class="addColumn btn btn-primary btn-md" onclick="addColumn()">ADD</button>
        </form>
    <?php }

    if ($_POST["request_type"] == 30) {
        $table_name = $_POST['table_name'];
        $column_name = $_POST['column_name'];
        $column_type = $_POST['column_type'];
        if (isset($table_name) && isset($column_name) && isset($column_type)) {
            foreach ($cmpname as $dbname122tk) {
                if ($column_type == 'VARCHAR' || $column_type == 'INT') {
                    $column = $column_name . ' ' . $column_type . '(255)';
                } else {
                    $column = $column_name . ' ' . $column_type;
                }
                $concronsk = mysqli_connect($host, $user, $password, $dbname122tk);
                $query = "ALTER TABLE $table_name ADD $column  NOT NULL";
                $result = $concronsk->query($query);
            }
        }
    }
    if ($_POST["request_type"] == 31) { ?>
        <table class="table table-bordered" >  
            <?php foreach ($cmpname as $dbname122tk) {
                $concronsk = mysqli_connect($host, $user, $password, $dbname122tk);
                $query = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='" . $dbname122tk . "' GROUP BY TABLE_NAME";
                $result = $concronsk->query($query);
                if ($dbname122tk == $demodb3) {
                    $sdsd = 0;
                    $query11 = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='" . $dbname122tk . "' GROUP BY TABLE_NAME";
                    $result11 = $concronsk->query($query11);
                    while ($data11 = $result11->fetch_assoc()) {
                        $sdsd++;
                    }
                    echo "<tr class='danger'> <th>DB Name </th><th colspan='" . $sdsd . "'>Table Name </th></tr>";
                }
                echo " <tr class='info'> <td class='danger'>" . $dbname122tk . " </td>";
                while ($data = $result->fetch_assoc()) {
                    $query1 = "select count(*) as sksk from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='" . $dbname122tk . "' AND TABLE_NAME='" . $data['TABLE_NAME'] . "' GROUP BY TABLE_NAME";
                    $result1 = $concronsk->query($query1);
                    $data1 = $result1->fetch_assoc();
                    echo "<td>" . $data['TABLE_NAME'] . "(" . $data1['sksk'] . ")</td>";
                    $count++;
                }
                echo "</tr>";
            } ?>
      </table>
    <?php }

    if ($_POST["request_type"] == 32) { ?>
        <form method="post" id="addColumn"> 
        Table : <select class="dbname" name="table_name">
          <?php
          $concronsk = mysqli_connect($host, $user, $password, $demodb3);
          $query21 = "select * from INFORMATION_SCHEMA.COLUMNS  where TABLE_SCHEMA='$demodb3'  GROUP BY TABLE_NAME";
          $result21 = $concronsk->query($query21);
          while ($data21 = $result21->fetch_assoc()) {
              echo "<option>" . $data21['TABLE_NAME'] . "</option>";
          }
          ?>
          <select>
            Column Name : <input type="text" name="column_name">
            Column Type<select name="column_type">
              <option value="VARCHAR">VARCHAR</option>
              <option value="INT">INT</option>
              <option value="DATE">DATE</option>
              <option value="DATETIME">DATETIME</option>
            </select>
            <input type="hidden" name="request_type" id="request_type" readonly value="30" />  
            <button type="button" class="addColumn btn btn-primary btn-md" onclick="addColumn()">ADD</button>
          </form>
          <?php }
    if ($_POST["request_type"] == 33) { ?>
          <form method="post" id="addColumn"> 
            Client Database : <select class="dbname" name="dbname">
              <?php
              echo "<option>ALL</option>";
              foreach ($cmpname as $dbname122tk) {
                  echo "<option>" . $dbname122tk . "</option>";
              }
              foreach ($cmpname2 as $dbname122tk) {
                  echo "<option>" . $dbname122tk . "</option>";
              }
              ?>
              <select>
               SQL : <textarea name="sqlquerry" rows="2" cols="50"></textarea>
               <input type="hidden" name="request_type" id="request_type" readonly value="34" />  
               <button type="button" class="addColumn btn btn-primary btn-md" onclick="addColumn()">RUN</button>
             </form>
             <?php }
    if ($_POST["request_type"] == 34) {
        $dbname = mysqli_real_escape_string($con, $_POST["dbname"]);
        $sqlquerry = mysqli_real_escape_string($con, $_POST["sqlquerry"]);
        if ($dbname == 'ALL') {
            foreach ($cmpname as $dbname122tk) {
                $concronsk = mysqli_connect($host, $user, $password, $dbname122tk);
                $sql = mysqli_query($concronsk, $sqlquerry);
                echo $concronsk->error;
            }
            foreach ($cmpname2 as $dbname122tk) {
                $concronsk = mysqli_connect($host, $user, $password, $dbname122tk);
                $sql = mysqli_query($concronsk, $sqlquerry);
                echo $concronsk->error;
            }
        } else {
            $concronsk = mysqli_connect($host, $user, $password, $dbname);
            $sql = mysqli_query($concronsk, $sqlquerry);
            echo $concronsk->error;
        }
        echo $sql;
    }
    if ($_POST["request_type"] == 35) {
        $query = "SELECT * FROM tbl_login WHERE Usr_Name = '" . $_POST["Usr"] . "'";
        $result = mysqli_query($con, $query);
        $row = mysqli_fetch_array($result);
        echo openssl_encrypt($row['Password'], "AES-128-CTR", 'qi', 0, '1234567891011121');
    }
}

if ($_POST["request_type"] == 37) { ?>
    <form method="post" id="addColumn"> 
        Client Database : <select class="dbname" name="dbname">
          <?php foreach ($cmpname3 as $dbname122tk) {
              echo "<option>" . $dbname122tk . "</option>";
          } ?>
        <select>
        Password: <input type="password" name="password" id="password"/>  
        <input type="hidden" name="request_type" id="request_type" readonly value="38" />  
        <button type="button" class="addColumn btn btn-success btn-md" onclick="addColumn()">Process</button>
    </form>
<?php }
if ($_POST["request_type"] == 38) {
    if ($_POST['password'] == $password_for_data_flush) { ?>
        <form method="post" id="addColumn2"> 
          <input type="hidden" name="dbname" id="dbname" readonly value="<?php echo $_POST['dbname']; ?>" />  
          <input type="hidden" name="request_type" id="request_type" readonly value="39" />  
          <button type="button" class="addColumn1 btn btn-danger btn-md" onclick="addColumn2()">With Configuration(Complete)</button>
        </form>
        <br>
        <br>
        <br>
        <br>
        <form method="post" id="addColumn3"> 
          <input type="hidden" name="dbname" id="dbname" readonly value="<?php echo $_POST['dbname']; ?>" />  
          <input type="hidden" name="request_type" id="request_type" readonly value="40" />  
          <button type="button" class="addColumn2 btn btn-info btn-md" onclick="addColumn3()">Without Configuration</button>
        </form>
    <?php }
}
//With Configuration(Complete)
if ($_POST["request_type"] == 39) {
    $username = $_POST['dbname'];

    $result = mysqli_query($con, "SELECT * FROM tbl_login WHERE Usr_Name = '" . $username . "'");
    $row = mysqli_fetch_array($result);
    $Usr_db = $row["Usr_db"];

    // $Usr_db1='imconnect_pump_demo';
    if (strlen($Usr_db) !== 0) {
        $concronsk = mysqli_connect($host, $user, $password, $Usr_db);
        ($result2 = mysqli_query($con, "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='" . $Usr_db . "' AND TABLE_NAME not in ('tbl_account_type') GROUP BY TABLE_NAME")) or die($con->error);
        while ($row = mysqli_fetch_array($result2)) {
            // echo "TRUNCATE '".$row['TABLE_NAME']."'";
            // echo '<br>';
            mysqli_query($concronsk, "TRUNCATE " . $row['TABLE_NAME'] . " ") or die($concronsk->error);
        }
        echo $_POST['dbname'] . ' - Done With Configuration(Complete)';
        mysqli_query($con, "INSERT INTO `tbl_record` ( `user`, `date`, `narration`) VALUES ('" . $_POST['dbname'] . "', '" . date('Y-m-d') . "', 'With Configuration(Complete)');");
    }
}
//Without Configuration
if ($_POST["request_type"] == 40) {
    $username = $_POST['dbname'];
    $result = mysqli_query($con, "SELECT * FROM tbl_login WHERE Usr_Name = '" . $username . "'");
    $row = mysqli_fetch_array($result);
    $Usr_db = $row["Usr_db"];

    // $Usr_db1='imconnect_pump_demo';
    if (strlen($Usr_db) !== 0) {
        $concronsk = mysqli_connect($host, $user, $password, $Usr_db);
        ($result2 = mysqli_query(
            $con,
            "select * from 
                INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='" . $Usr_db . "'  
             AND 
                TABLE_NAME not in ('tbl_account_type','tbl_ledger','tbl_meter','tbl_nozzle','tbl_oil','tbl_petrocard','tbl_product','tbl_stamping','tbl_swipe','tbl_tank','tbl_vehicle') 
            GROUP BY TABLE_NAME"
        )) or die($con->error);
        while ($row = mysqli_fetch_array($result2)) {
            // echo "TRUNCATE '".$row['TABLE_NAME']."'";
            // echo '<br>';
            mysqli_query($concronsk, "TRUNCATE " . $row['TABLE_NAME'] . "") or die($concronsk->error);
        }
        echo $_POST['dbname'] . ' - Done Without Configuration';
        mysqli_query($con, "INSERT INTO `tbl_record` ( `user`, `date`, `narration`) VALUES ('" . $_POST['dbname'] . "', '" . date('Y-m-d') . "', 'Without Configuration');");
    }
}
?>
