<?php

/**
 * ComeCondo Tag Filter 20201023
 */

/*
 Plugin Name: ComeCondo Submissions Insight 
 */



function my_admin_menu() {

add_menu_page(
# __( 'Submissions Insight Page', 'my-textdomain' ),
'Submissions Insight Page',
# __( '客户洞察', 'my-textdomain' ),
'客户洞察',
'manage_options',
'submissions-insight-page',
'submission_insight_page_contents',
'dashicons-groups',
30
);

add_submenu_page('submissions-insight-page', 'Submissions Detail', '客户明细', 'manage_options', 'submissions_details_page', 'submissions_details_contents');
add_submenu_page('submissions-insight-page', 'Add or Update Submissions', '增加/删除', 'manage_options', 'add_submission_page', 'add_submission_contents');
add_submenu_page('submissions-insight-page', 'Submission Visits Analysis', '客户访问分析', 'manage_options', 'submission_visits_analysis', 'submission_visits_analysis_contents');
add_submenu_page('submissions-insight-page', 'Submission Contact Summary', '客户接触记录汇总', 'manage_options', 'submission_contact_summary', 'submission_contact_summary_contents');
add_submenu_page('submissions-insight-page', 'Visited Pages Analysis', '访问页面分析', 'manage_options', 'visited_page_analysis', 'visited_page_analysis_contents');
add_submenu_page('submissions-insight-page', 'Searched Pages Analysis', '搜索页面分析', 'manage_options', 'searched_page_analysis', 'searched_page_analysis_contents');
add_submenu_page('submissions-insight-page', 'IP Searching History', 'IP搜索记录', 'manage_options', 'ip_searching_history', 'ip_searching_history_contents');
add_submenu_page('submissions-insight-page', 'Client Worksheets', '客户提交的Worksheets', 'manage_options', 'clients_submitted_worksheets', 'clients_submitted_worksheets_contents');
add_submenu_page('submissions-insight-page', 'Home Price Evaluation', '房产估价记录', 'manage_options', 'home_price_evaluation_history', 'home_price_evaluation_history_contents');

}

add_action( 'admin_menu', 'my_admin_menu' );

function submission_insight_page_contents() {
    global $wpdb;
?>

<h1>
<?php esc_html_e( '注册用户切分', 'my-plugin-textdomain' ); ?>
</h1>
<form action="https://comecondo.com/wp-admin/admin.php?page=submissions-insight-page" method="post">
<div class="head_div"><label>城市</label>
<select name="city" id="city"><option value="%">全选</option><option value="toronto">Toronto Downtown多伦多市区</option><option value="Scarborough">Scarborough士嘉堡</option><option value="vaughan">Vaughan旺市</option><option value="mississauga">Mississauga密西沙加</option><option value="Etobicoke">Etobicoke怡陶碧谷</option><option value="oakville">Oakville奥克维尔</option><option value="North York">North York北约克</option><option value="markham">Markham万锦</option><option value="Richmond Hill">Richmond Hill列治文山</option><option value="Burlington">Burlington</option><option value="Milton">Milton</option><option value="Kitchener">Kitchener</option><option value="Waterloo">Waterloo</option><option value="Cambridge">Cambridge</option><option value="Hamilton">Hamilton</option><option value="Whitby">Whitby</option><option value="Pickering">Pickering</option><option value="Oshawa">Oshawa</option><option value="Aurora">Aurora</option><option value="Newmarket">Newmarket</option></select>
<script type="text/javascript">document.getElementById('city').value = "<?php echo $_POST['city'];?>";</script></div>
<div class="text"><label>楼盘</label>
<input name="property"  type="text" id="property"></input>
<script type="text/javascript">document.getElementById('property').value = "<?php echo $_POST['property'];?>";</script></div>
<div class="head_div"><label>是否经纪</label>
<select name="broker" id="broker"><option value="%">均可</option><option value="Y">是</option><option value="N">否</option></select>
<script type="text/javascript">document.getElementById('broker').value = "<?php echo $_POST['broker'];?>";</script></div>

<div class="head_div"><label>是否有微信联系</label>
<select name="wechat" id="wechat"><option value="%">均可</option><option value="Y">是</option><option value="N">否</option></select>
<script type="text/javascript">document.getElementById('wechat').value = "<?php echo $_POST['wechat'];?>";</script></div>

<div class="head_div"><label>要Condo</label>
<select name="condo" id="condo"><option value="%">均可</option><option value="1">是</option><option value="0">否</option></select>
<script type="text/javascript">document.getElementById('condo').value = "<?php echo $_POST['condo'];?>";</script></div>
<div class="head_div"><label>要Townhouse</label>
<select name="townhouse" id="townhouse"><option value="%">均可</option><option value="1">是</option><option value="0">否</option></select>
<script type="text/javascript">document.getElementById('townhouse').value = "<?php echo $_POST['townhouse'];?>";</script></div>
<div class="head_div"><label>要Detached</label>
<select name="detached" id="detached"><option value="%">均可</option><option value="1">是</option><option value="0">否</option></select>
<script type="text/javascript">document.getElementById('detached').value = "<?php echo $_POST['detached'];?>";</script></div>
<input class="head_div" type="submit" value="查询">
</form>
<?php
$city      = $_POST['city'];
$property  = $_POST['property'];
$broker    = $_POST['broker'];
$wechat    = $_POST['wechat'];
$condo     = $_POST['condo'];
$townhouse = $_POST['townhouse'];
$detached  = $_POST['detached'];
echo $city;
# $emails = $wpdb->get_results("select email from (select distinct email, tag_value as city from cc_submission_tags where tag_name='City' union select distinct email, f.city from  cc_submission_tags t join cc_property_full f on t.tag_value=f.property_name union select distinct a.email, f.city from (select distinct email, h.property_name from cc_submission_tags t join cc_view_visitors_property_history h on t.tag_value=h.ip where t.tag_name='IP') a join cc_property_full f on a.property_name=f.property_name ) c where city in ('{$city}');");
# $emails = $wpdb->get_results("select distinct email from cc_submissions where city like '{$city}' and IfBroker like '{$broker}';");
# $emails = $wpdb->get_results("select distinct sb.email, concat(d.firstname,' ',d.lastname) as name from  cc_submissions sb left join cc_property_full f on sb.property_name=f.property_name left join cc_submission_details d on sb.email=d.email where sb.city like '{$city}' and sb.property_name like '%{$property}%' and IfBroker like '{$broker}' and WeChat like '{$wechat}' and f.Townhouse like '{$townhouse}' and f.Detached like '{$detached}' and case when f.Townhouse=0 and f.Detached=0 then 1 else 0 end like '{$condo}' order by 2; ");
$emails = $wpdb->get_results("select distinct email, name from (select sb.email, concat(d.firstname,' ',d.lastname) as name from  cc_submissions sb left join cc_property_full f on sb.property_name=f.property_name left join cc_submission_details d on sb.email=d.email where sb.city like '{$city}' and sb.property_name like '%{$property}%' and IfBroker like '{$broker}' and WeChat like '{$wechat}' and f.Townhouse like '{$townhouse}' and f.Detached like '{$detached}' and case when f.Townhouse=0 and f.Detached=0 then 1 else 0 end like '{$condo}' union select sb.email, concat(d.firstname,' ',d.lastname) as name from cc_submissions sb left join cc_submission_details d on sb.email=d.email where sb.city like '{$city}' and IfBroker like '{$broker}' and WeChat like '{$wechat}' and sb.IP is null and sb.City is not null) u order by 2; ");
echo " " . count($emails) . " 个结果";
?>  <br />
<style>
th, td {
  border: 1px solid black;
  border-collapse:  collapse;
}
input {
  border: none;
  color: white;
  background: #35A340;
  border-radius: 4px;
}
</style>
<table frame='border'  cellpadding='10'>
<?php
echo "<th>Email</th>";
echo "<th>Name</th>";
foreach ($emails as $email){
?>
<col align='left'</col>
<script>
$('td').click(function(){
			copyTextToClipboard($(this).html());
});

function copyTextToClipboard(text) {
  var textArea = document.createElement("textarea");

  textArea.style.position = 'fixed';
  textArea.style.top = 0;
  textArea.style.left = 0;

  textArea.style.width = '2em';
  textArea.style.height = '2em';

  textArea.style.padding = 0;

  textArea.style.border = 'none';
  textArea.style.outline = 'none';
  textArea.style.boxShadow = 'none';

  textArea.style.background = 'transparent';

  textArea.value = text;

  document.body.appendChild(textArea);

  textArea.select();

  try {
    var successful = document.execCommand('copy');
    var msg = successful ? 'successful' : 'unsuccessful';
    console.log('Copying text command was ' + msg);
  } catch (err) {
    console.log('Oops, unable to copy');
  }

  document.body.removeChild(textArea);
}
</script>
<?php
  echo "<td>" . $email->email . "</td>";
  echo "<td>" . $email->name . "</td>";
}
?>
</table>

<form action="https://comecondo.com/wp-admin/admin.php?page=submissions-insight-page" method="post">
<div class="head_div"><label>Tag</label>
<select name="tag" id="tag"><option value="City">City</option><option value="From">From</option><option value="Property_Type">Property Type</option></select>
<script type="text/javascript">document.getElementById('tag').value = "<?php echo $_POST['tag'];?>";</script></div>
<div class="head_div"><label>Tag Value</label>
<select name="tag_value" id="tag_value"><option value="%">均可</option><option value="1">是</option><option value="0">否</option></select>
<script type="text/javascript">document.getElementById('townhouse').value = "<?php echo $_POST['townhouse'];?>";</script></div>
<div class="head_div"><label>要Detached</label>
<select name="detached" id="detached"><option value="%">均可</option><option value="1">是</option><option value="0">否</option></select>
<script type="text/javascript">document.getElementById('detached').value = "<?php echo $_POST['detached'];?>";</script></div>
<input class="head_div" type="submit" value="查询">
</form>
<?php
$tag        = $_POST['tag'];
$tag_value  = $_POST['tag_value'];
}

function submissions_details_contents() {
    global $wpdb;
    $orderCol = 10;
?>
<h1>
<?php esc_html_e( '用户详细信息', 'my-plugin-textdomain' ); ?>
</h1>
<h3>
<?php esc_html_e( '(不含僵尸客户)', 'my-plugin-textdomain' ); ?>
</h3>
<?php
# $rows = $wpdb->get_results("select distinct id, firstname, lastname, sex, email, phone, first_logdate, first_logtime, whereFrom from cc_submission_details order by 7 desc, 8 desc;");
$rows = $wpdb->get_results("select distinct id, firstname, lastname, sex, if_broker, d.email, phone, concat(first_logdate,' ', first_logtime) as first_login, whereFrom, latest_contact from cc_submission_details d left join (select email, max(concat(contact_date, ' ', contact_time)) as latest_contact from cc_submissions_contact_history group by email) h on d.email=h.email where d.email not in (select email from cc_submission_tags where tag_name='Zombie' and tag_value='Y') order by {$orderCol} desc;");
echo " " . count($rows) . " 个注册用户";
?> <br />
<style>
th, td {
  border: 1px solid black;
  border-collapse:  collapse;
}
input {
  border: none;
  color: white;
  background: #35A340;
  border-radius: 4px;
}
</style>
<script type="text/javascript">
function reOrder(columnIndex) {
    
}

function bindDeactivate() {
  window.alert("to Bind Deactivate");
  $('.deactivate').click(function() {
    var $this = $(this);
    var $tr = $this.closest('tr');
    var action = $tr.hasClass('deactivated') ? 'activate' : 'deactivate';

    if (confirm('Are you sure you want to ' + action + ' this entry?')) {
      $tr.toggleClass('deactivated');
      $this.text(function(i, t) {
        return t == 'Deactivate' ? 'Activate' : 'Deactivate';
      });
    }
  });
}

function sortRows(columnIndex) {
    table = document.getElementById("sortable_details");
    var allForms = table.getElementsByTagName("form");
    var allRows = table.getElementsByTagName("TR");
    var totalFormsCount = allForms.length;
    var totalRowCount = allRows.length;
    // window.alert("Total Forms ".concat(totalFormsCount.toString()));
    // window.alert("Total Inputs ".concat(totalInputsCount.toString()));
    // window.alert("This is column ".concat(columnIndex.toString()));
    var first_val = allRows[1].getElementsByTagName("TD")[columnIndex].innerHTML.toLowerCase();
    var second_val = allRows[2].getElementsByTagName("TD")[columnIndex].innerHTML.toLowerCase();
    var if_loop = 1;
    var i = 0;
    var max_i = 0;
    var eve;
    var con;
    var firstForm;
    var secondForm;
    var firstRow;
    var secondRow;
    if (first_val > second_val) {
        alert("Ascent");
        if_loop = 1;
        max_i = 0;
        loop_for1:
        while(if_loop==1){
            // window.alert("Total Forms 2 ".concat(totalFormsCount.toString()));
            if_loop = 0;
            loop_for2:
            for (i=0; i<totalFormsCount;i++){
                // window.alert("Current Row ".concat(i.toString()));
                eve = allRows[i+1].getElementsByTagName("td")[columnIndex].innerHTML.toLowerCase();
                con = allRows[i+2].getElementsByTagName("td")[columnIndex].innerHTML.toLowerCase();
                // window.alert("Check ".concat(eve));
                if (eve > con) {
                    // window.alert("Before Swtich ".concat(allRows[i].getElementsByTagName("TD")[columnIndex].innerHTML.toLowerCase()));
                    firstForm  = allForms[i];
                    secondForm = allForms[i+1];
                    firstRow  = allRows[i+1];
                    secondRow = allRows[i+2];
                    firstForm.parentNode.insertBefore(secondForm, firstForm);
                    firstRow.parentNode.insertBefore(secondRow, firstRow);
                    // window.alert("After Swtich ".concat(allRows[i].getElementsByTagName("TD")[columnIndex].innerHTML.toLowerCase()));
                    if_loop = 1;
                    break loop_for2;
                }
            }
/*
            if (max_i < i) {
                max_i = i;
                window.alert("New Max i Value ".concat(max_i.to_String()));
            }
*/
            if (i >= totalFormsCount-1){
                alert("To Break from ".concat(i.to_String()));
                break loop_for1;
            }
        }
        window.alert("Loop Finishes in if ".concat(if_loop.toString()));
    }
    else {
        alert("Descent");
        if_loop = 1;
        max_i = 0;
        loop_else1:
        while(if_loop == 1){
            // window.alert("Total Forms 2 ".concat(totalFormsCount.toString()));
            if_loop = 0;
            // window.alert("Into Another While Loop ".concat(if_loop.toString()));
            loop_else2:
            for (i=0; i<totalFormsCount;i++){
                // window.alert("Current Row ".concat(i.toString()));
                eve = allRows[i+1].getElementsByTagName("td")[columnIndex].innerHTML.toLowerCase();
                con = allRows[i+2].getElementsByTagName("td")[columnIndex].innerHTML.toLowerCase();
                if (eve < con) {
                    // window.alert("Before Swtich ".concat(allRows[i].getElementsByTagName("TD")[columnIndex].innerHTML.toLowerCase()));
                    firstForm  = allForms[i];
                    secondForm = allForms[i+1];
                    firstRow  = allRows[i+1];
                    secondRow = allRows[i+2];
                    firstForm.parentNode.insertBefore(secondForm, firstForm);
                    firstRow.parentNode.insertBefore(secondRow, firstRow);
                    // secondForm.parentNode.appendChild(firstForm);
                    // secondRow.parentNode.appendChild(firstRow);
                    // window.alert("After Swtich ".concat(allRows[i].getElementsByTagName("TD")[columnIndex].innerHTML.toLowerCase()));
                    if_loop = 1;
                    break loop_else2;
                }
            }
/*
            if (max_i < i) {
                max_i = i;
                window.alert("New Max i Value ".concat(max_i.to_String()));
            }
*/
            if (i >= totalFormsCount-1){
                alert("To Break from ".concat(i.to_String()));
                break loop_else1;
            }
            // window.alert("Go to Another While Loop ".concat(if_loop.toString()));
        }
        window.alert("Loop Finishes in else ".concat(if_loop.toString()));
    }
/*
    new_table = document.createElement("table");
    new_table.appendChild(allRows[0]);
    for (var j=0; j<totalFormsCount-2;j++){
        allForms[j].setAttribute("id", "row_form");
        allForms[j].setAttribute("action", "submission_contact_details.php");
        allForms[j].setAttribute("method", "post");
        allForms[j].setAttribute("target", "_blank");
        // allForms[j].appendChild(allRows[j+1]);
        new_table.appendChild(allForms[j]);
    }
    new_table.setAttribute("id", "sortable_details");
    table.parentNode.replaceChild(new_table, table);
*/
    bindDeactivate(); 
}
</script>
<table id='sortable_details' frame='border'  cellpadding='10'>
<th onclick="sortRows(0)">First Name</th>
<?php
echo "<th>Last Name</th>";
echo "<th>Gender</th>";
echo "<th>Broker</th>";
echo "<th>Email</th>";
echo "<th>Phone</th>";
echo "<th>From</th>";
?>
<th onclick="sortRows(7)">First Contact</th>
<?php
echo "<th>Latest Update</th>";
echo "<th>Contact Details</th>";
foreach ($rows as $row){
?>
<!-- <col align='left'</col> -->
<form id="row_form" action="submission_contact_details.php" method="post" target="_blank">
<?php
    echo "<tr>";
    echo "<td>" . $row->firstname . "</td>";
    echo "<td>" . $row->lastname . "</td>";
    echo "<td>" . $row->sex . "</td>";
    echo "<td>" . $row->if_broker . "</td>";
    echo "<td><input id='input_email' type='text' name='email' value='" . $row->email . "' readonly></input></td>";
    echo "<td>" . $row->phone . "</td>";
    echo "<td>" . $row->whereFrom . "</td>";
    echo "<td>" . $row->first_login . "</td>";
    echo "<td>" . $row->latest_contact . "</td>";
    echo "<td><input id='submit_email' type='submit' name='details' value='Contact History'></td>";
    echo "</tr>";
?></form>
<?php
}?>
</table>
<?php
}


function add_submission_contents() {
    global $wpdb;
?>
<h1>
<?php esc_html_e('增加用户', 'my-plugin-textdomain'); 
?>
</h1>
<form action="" method="post">
<input name="firstname", type="text", value="First Name">
<input name="lastname", type="text", value="Last Name">
<input name="email", type="text", value="Email">
<input name="sex", type="text", value="Sex">
<input name="if_broker", type="text", value="If_Broker">
<input name="firstlogdate", type="date", value="First Login Date">
<input name="firstlogtime", type="time", value="First Login Time">
<input name="phone", type="tel", value="Phone Number">
<input name="from", type="text", value="From">
<input type="submit", name="Details_Submit" value="添加用户">
</form>
<?php

if(isset($_POST['Details_Submit'])){
  $firstname    = $_POST["firstname"];
  $lastname     = $_POST["lastname"];
  $email        = $_POST["email"];
  $sex          = $_POST["sex"];
  $if_broker    = $_POST["if_broker"];
  $firstlogdate = $_POST["firstlogdate"];
  $firstlogtime = $_POST["firstlogtime"];
  $phone        = $_POST["phone"];
  $from         = $_POST["from"];

  if($firstname    =="First Name"){$firstname="";}
  if($lastname     =="Last Name"){$lastname="";}
  if($email        =="Email"){$email="";}
  if($sex          =="Sex"){$sex="";}
  if($if_broker    =="If_Broker"){$if_broker="";}
  if($firstlogdate =="First Login Date"){$firstlogdate="";}
  if($firstlogtime =="First Login Time"){$firstlogtime="";}
  if($phone        =="Phone Number"){$phone="";}
  if($from         =="From"){$from="";}

  $existing_emails = $wpdb->get_results("select email from cc_submission_details where email = '{$email}';");
  if ($wpdb->num_rows>0){
      echo '<script>alert("This Email exists!")</script>';
  }
  else {
      $wpdb->insert('cc_submission_details', array('email'=>$email, 'firstname'=>$firstname, 'lastname'=>$lastname, 'sex'=>$sex, 'if_broker'=>$if_broker, 'first_logdate'=>$firstlogdate, 'first_logtime'=>$firstlogtime, 'phone'=>$phone, 'whereFrom'=>$from));
#      $wpdb->insert('cc_submission_tags', array('email'=>$email, 'tag_name'=>'IP', 'tag_value'=>$ip));
  }
}
?>

<form action="" method="post">
<input name="tag_email", type="text", value="Email">
<select name="tag_name"><option value="Property">Property</option><option value="Property_Type">房屋类型</option><option value="IP">IP</option><option value="City">City</option><option value="Broker">If Broker</option><option value="WeChat">有微信</option><option value="Zombie">僵尸</option></select>
<input name="tag_value", type="text", value="Tag Value">
<input type="submit", name="Tag_Submit", value="提交Tag">
</form>
<?php
if(isset($_POST['Tag_Submit'])){
    $tag_email    = $_POST["tag_email"];
    $tag_name     = $_POST["tag_name"];
    $tag_value    = $_POST["tag_value"];
    $wpdb->insert('cc_submission_tags', array('email'=>$tag_email, 'tag_name'=>$tag_name, 'tag_value'=>$tag_value));
}
?>
<hr>
<h1>删除用户</h1>
<form action="" method="post">
<input name="deleted_email", type="text", size="35", value="Email to Delete">
<script>
function click_to_confirm(e){
    if(!confirm('确定删除这个Email吗？')){
        e.preventDefault();
    }
}
</script>
<input type="submit", name="Delete_Submit", value="删除", onclick="click_to_confirm(event)" />
</form>
<?php
if(isset($_POST['Delete_Submit'])){
    $email_to_delete = $_POST['deleted_email'];
    $wpdb->delete('cc_submissions', array('email'=>$email_to_delete));
    $wpdb->delete('cc_submission_details', array('email'=>$email_to_delete));
    $wpdb->delete('cc_submission_tag_widetable', array('email'=>$email_to_delete));
    $wpdb->delete('cc_submission_tags', array('email'=>$email_to_delete));
}
}


function submission_contact_summary_contents(){
    global $wpdb;

?>
<style>
table.table-style-two {
        font-family: verdana, arial, sans-serif;
        font-size: 11px;
        color: #333333;
        border-width: 1px;
        border-color: #3A3A3A;
        border-collapse: collapse;
    }
 
    table.table-style-two th {
        border-width: 1px;
        padding: 8px;
        border-style: solid;
        border-color: #517994;
        background-color: #B2CFD8;
    }
 
    table.table-style-two tr:hover td {
        background-color: #DFEBF1;
    }
 
    table.table-style-two td {
        border-width: 1px;
        padding: 8px;
        border-style: solid;
        border-color: #517994;
        background-color: #ffffff;
    }
</style>

<h1>
<?php esc_html_e('客户接触记录汇总', 'my-plugin-textdomain');
$submission_contacts = $wpdb->get_results("select h.email, d.firstname,d.lastname, h.contact_date, h.notes from cc_submissions_contact_history h left join cc_submission_details d on h.email=d.email order by 1,3;");
?>
</h1>
<script>
function downloadCSV(csv, filename) {
    var csvFile;
    var downloadLink;

    // CSV file
    csvFile = new Blob([csv], {type: "text/csv"});

    // Download link
    downloadLink = document.createElement("a");

    // File name
    downloadLink.download = filename;

    // Create a link to the file
    downloadLink.href = window.URL.createObjectURL(csvFile);

    // Hide download link
    downloadLink.style.display = "none";

    // Add the link to DOM
    document.body.appendChild(downloadLink);

    // Click download link
    downloadLink.click();
}
function exportTableToCSV(filename) {
    var csv = [];
    var rows = document.querySelectorAll("table tr");
    
    for (var i = 0; i < rows.length; i++) {
        var row = [], cols = rows[i].querySelectorAll("td, th");
        
        for (var j = 0; j < cols.length; j++) 
            row.push(cols[j].innerText);
        
        csv.push(row.join("^"));        
    }

    // Download CSV file
    downloadCSV(csv.join("\n"), filename);
}
</script>
<button onclick="exportTableToCSV('Customer_Contact_History.csv')">导出客户接触记录</button>
<table class="table-style-two" id="top_pages_table">
  <thead>
    <tr>
        <th>Email</th>
        <th>Firstname</th>
        <th>Lastname</th>
        <th>Date</th>
        <th>Notes</th>
    </tr>
  </thead>
  <tbody>
<?php
  foreach($submission_contacts as $contact){
?>
    <tr>
        <td><?php echo $contact->email ?></td>
        <td><?php echo $contact->firstname ?></td>
        <td><?php echo $contact->lastname ?></td>
        <td><?php echo $contact->contact_date ?></td>
        <td><?php echo $contact->notes ?></td>
    </tr>
<?php
  }
?>

</table>
<?php
}


function submission_visits_analysis_contents(){
    global $wpdb;

?>
<style>
table.table-style-two {
        font-family: verdana, arial, sans-serif;
        font-size: 11px;
        color: #333333;
        border-width: 1px;
        border-color: #3A3A3A;
        border-collapse: collapse;
    }
 
    table.table-style-two th {
        border-width: 1px;
        padding: 8px;
        border-style: solid;
        border-color: #517994;
        background-color: #B2CFD8;
    }
 
    table.table-style-two tr:hover td {
        background-color: #DFEBF1;
    }
 
    table.table-style-two td {
        border-width: 1px;
        padding: 8px;
        border-style: solid;
        border-color: #517994;
        background-color: #ffffff;
    }
</style>
<h1>
<?php date_default_timezone_set("America/New_York"); ?>
<?php esc_html_e('今天('.date("Y/m/d").')客户访问的页面', 'my-plugin-textdomain');
$submission_visits = $wpdb->get_results("select distinct concat(c.city,'-',c.region,'-',c.country) as ip_location, submission, concat(b.firstname,' ',b.lastname) as name, page_name, page_id from cc_view_visitors_property_history a left join cc_submission_details b on a.submission=b.email left join cc_ip_city c on a.ip=c.ip where a.logdate>now()-interval 1 day and page_name is not null and page_name<>'Home' and submission is not null order by 3;");
?>
</h1>
<table class="table-style-two" id="top_pages_table">
  <thead>
    <tr>
        <th>IP Location</th>
        <th>Email</th>
        <th>Name</th>
        <th>Page</th>
    </tr>
  </thead>
  <tbody>
<?php
  foreach($submission_visits as $v){
?>
    <tr>
      <td><?php echo $v->ip_location ?></td>
      <td><?php echo $v->submission ?></td>
      <td><?php echo $v->name ?></td>
      <td><a href='comecondo.com/?p=<?php echo $v->page_id ?>'><?php echo $v->page_name ?></a></td>
    </tr>
<?php
  }
?>
  </tbody>
</table>


<h1>
<?php esc_html_e('最近两天客户访问的页面', 'my-plugin-textdomain');
$sub_visits = $wpdb->get_results("select distinct concat(c.city,'-',c.region,'-',c.country) as ip_location, submission, concat(b.firstname,' ',b.lastname) as name, page_name, page_id from cc_view_visitors_property_history a left join cc_submission_details b on a.submission=b.email left join cc_ip_city c on a.ip=c.ip where a.logdate>now()-interval 2 day and page_name is not null and page_name<>'Home' and submission is not null order by 3;");
?>
</h1>
<table class="table-style-two" id="top_pages_table">
  <thead>
    <tr>
        <th>IP Location</th>
        <th>Email</th>
        <th>Name</th>
        <th>Page</th>
    </tr>
  </thead>
  <tbody>
<?php
  foreach($sub_visits as $v){
?>
    <tr>
      <td><?php echo $v->ip_location ?></td>
      <td><?php echo $v->submission ?></td>
      <td><?php echo $v->name ?></td>
      <td><a href='comecondo.com/?p=<?php echo $v->page_id ?>'><?php echo $v->page_name ?></a></td>
    </tr>
<?php
  }
?>
  </tbody>
</table>


<?php
}



function clients_submitted_worksheets_contents() {
  function sortByTime($a, $b){
    return $b->getCTime() - $a->getCTime();
  }
  
  $secure_folder = "dk7kdoeyIkd6ll1dk/";
  $path = "/var/www/html/wp-admin/".$secure_folder;
  $html_path = "/wp-admin/".$secure_folder;
  $iterator = new FilesystemIterator($path);
  $files_array = iterator_to_array($iterator, false);
  usort($files_array, 'sortByTime');
  foreach($files_array as $fileInfo){
    if($fileInfo->isFile()){
        $cTime = new DateTime();
        $cNow  = new DateTime();
        $cTime->setTimezone(new DateTimeZone('America/New_York'));
        $cNow->setTimezone(new DateTimeZone('America/New_York'));
        $cTime->setTimestamp($fileInfo->getCTime());
        $diff = $cTime->diff($cNow);
        $diffDays = (integer)$diff->format("%R%a");
        if ($diffDays<1) {
            echo '<i>&#127775</i>';
        }
        echo $cTime->format('Y-m-d H:i:s') . " <a href=" . $html_path . $fileInfo->getFileName() . " target='_blank'>" . $fileInfo->getFileName() ."</a>" .  "<br/>\n";
    }
    if($fileInfo->isDir()){
        $cTime = new DateTime();
        $cTime->setTimestamp($fileInfo->getMTime());
        echo $fileInfo->getFileName() . " dir Modified " . $cTime->format('Y-m-d h:i:s') . "<br/>\n";
    }
}
}



function home_price_evaluation_history_contents() {
    global $wpdb;
?>
    <div style="text-align:center"><h2>房产估价记录</h2></div><br><br>
<style>
table.table-style-two {
        font-family: verdana, arial, sans-serif;
        font-size: 11px;
        color: #333333;
        border-width: 1px;
        border-color: #3A3A3A;
        border-collapse: collapse;
    }
 
    table.table-style-two th {
        border-width: 1px;
        padding: 8px;
        border-style: solid;
        border-color: #517994;
        background-color: #B2CFD8;
    }
 
    table.table-style-two tr:hover td {
        background-color: #DFEBF1;
    }
 
    table.table-style-two td {
        border-width: 1px;
        padding: 8px;
        border-style: solid;
        border-color: #517994;
        background-color: #ffffff;
    }
</style>

<?php
     $evaluations = $wpdb->get_results("select concat(evaluation_date,' ',evaluation_time) as eval_time, owner_name,email,phone,ip,address,bedrooms, bathrooms, size, age, price from cc_home_price_evaluation_history order by id desc;");
?>
    <table class="table-style-two" id="top_pages_table">
        <thead>
            <tr>
                <th>评估时间</th>
                <th>姓名</th>
                <th>邮箱</th>
                <th>电话</th>
                <th>IP</th>
                <th>地址</th>
                <th>卧室</th>
                <th>卫生间</th>
                <th>面积</th>
                <th>房龄</th>
                <th>评估价格</th>
            </tr>
      </thead>
  <tbody>
<?php
    foreach($evaluations as $evl){
?>
        <tr>
            <td><?php echo $evl->eval_time ?></td>
            <td><?php echo $evl->owner_name ?></td>
            <td><?php echo $evl->email ?></td>
            <td><?php echo $evl->phone ?></td>
            <td><?php echo $evl->ip ?></td>
            <td><?php echo $evl->address ?></td>
            <td><?php echo $evl->bedrooms ?></td>
            <td><?php echo $evl->bathrooms ?></td>
            <td><?php echo $evl->size ?></td>
            <td><?php echo $evl->age ?></td>
            <td><?php echo $evl->price ?></td>
        </tr>
<?php
    }
?>
  </tbody>
  </table>
<?php
}

function ip_searching_history_contents() {
    global $wpdb;
?>
    <h1>IP搜索记录</h1>
    <label>IP</label>
    <form action="" method="post">
        <div>
            <input type="text" name="ipaddress" id="ipaddress" />
        </div>
        <div>
            <input type="submit" value="查询" />
        </div>
    </form>
<style>
table.table-style-two {
        font-family: verdana, arial, sans-serif;
        font-size: 11px;
        color: #333333;
        border-width: 1px;
        border-color: #3A3A3A;
        border-collapse: collapse;
    }

    table.table-style-two th {
        border-width: 1px;
        padding: 8px;
        border-style: solid;
        border-color: #517994;
        background-color: #B2CFD8;
    }

    table.table-style-two tr:hover td {
        background-color: #DFEBF1;
    }

    table.table-style-two td {
        border-width: 1px;
        padding: 8px;
        border-style: solid;
        border-color: #517994;
        background-color: #ffffff;
    }
</style>

<?php
    $ip = $_POST['ipaddress'];
    $query_1 = "select a.last_counter as date, a.engine, a.words, b.ip,c.email from wp_statistics_visitor b join wp_statistics_search a on a.visitor=b.id and a.last_counter=b.last_counter left join cc_submission_tags c on b.ip=c.tag_value where b.ip='{$ip}' order by 1 desc;";
    $query_2 = "select distinct h.ip,h.logtime, p.post_title from cc_visitors_history h join wp_posts p on h.page_id=p.id where h.ip='{$ip}' order by 2 desc;";
    $records_1 = $wpdb->get_results($query_1);
    $records_2 = $wpdb->get_results($query_2);
    echo ($ip);
?>
    <table class="table-style-two" id="top_pages_table">
        <thead>
            <tr>
                <th>日期</th>
                <th>搜索引擎</th>
                <th>页面</th>
            </tr>
        </thead>
  <tbody>
    <?php
      foreach($records_1 as $record){
?>
    <tr>
      <td><?php echo $record->date ?></td>
      <td><?php echo $record->engine ?></td>
      <td><?php echo str_replace('"','',str_replace('~','',str_replace('-','',str_replace('多伦多最新最全的楼花搜索引擎','',$record->words)))) ?></td>
    </tr>
<?php
  }
?>
  </tbody>
  <table class="table-style-two" id="top_pages_table">
        <thead>
            <tr>
                <th>访问时间</th>
                <th>页面</th>
            </tr>
        </thead>
  <tbody>
    <?php
      foreach($records_2 as $record){
?>
    <tr>
      <td><?php echo $record->logtime ?></td>
      <td><?php echo $record->post_title ?></td>
    </tr>
<?php
  }
?>
  </tbody>
<?php
}


function searched_page_analysis_contents() {
    global $wpdb;
?>
<style>
table.table-style-two {
        font-family: verdana, arial, sans-serif;
        font-size: 11px;
        color: #333333;
        border-width: 1px;
        border-color: #3A3A3A;
        border-collapse: collapse;
    }

    table.table-style-two th {
        border-width: 1px;
        padding: 8px;
        border-style: solid;
        border-color: #517994;
        background-color: #B2CFD8;
    }

    table.table-style-two tr:hover td {
        background-color: #DFEBF1;
    }

    table.table-style-two td {
        border-width: 1px;
        padding: 8px;
        border-style: solid;
        border-color: #517994;
        background-color: #ffffff;
    }
</style>
<h1>
<?php esc_html_e('页面搜索记录汇总', 'my-plugin-textdomain');
?>
</h1>
<hr size="5" color="blue">
<h2>
<?php esc_html_e('最新5天搜索数变化趋势', 'my-plugin-textdomain');
  $recent_daily_total = $wpdb->get_results("select last_counter as date, count(*) as daily_total_search from wp_statistics_search where last_counter>now()-interval 5 day group by last_counter order by 1;");
?>
</h2>
<table class="table-style-two" id="top_pages_table">
  <thead>
    <tr>
      <th>日期</th>
      <th>搜索数</th>
    </tr>
  </thead>
  <tbody>
    <?php
      foreach($recent_daily_total as $daily){
?>
    <tr>
      <td><?php echo $daily->date ?></td>
      <td><?php echo $daily->daily_total_search ?></td>
    </tr>
<?php
  }
?>
  </tbody>
</table>
<hr size="5" color="blue">
<h2>
<?php esc_html_e('最新350条搜索记录', 'my-plugin-textdomain');
  $most_recent_50 = $wpdb->get_results("select a.last_counter as date, a.engine, a.words, b.ip,c.email from wp_statistics_visitor b join wp_statistics_search a on a.visitor=b.id and a.last_counter=b.last_counter left join cc_submission_tags c on b.ip=c.tag_value order by a.id desc limit 350;");
?>
</h2>
<table class="table-style-two" id="top_pages_table">
  <thead>
    <tr>
      <th>日期</th>
      <th>搜索服务</th>
      <th>搜索命中页面</th>
      <th>IP</th>
      <th>Email</th>
    </tr>
  </thead>
  <tbody>
    <?php
      foreach($most_recent_50 as $visit){
?>
    <tr>
      <td><?php echo $visit->date ?></td>
      <td><?php echo $visit->engine ?></td>
      <td><?php echo str_replace('"','',str_replace('~','',str_replace('-','',str_replace('多伦多最新最全的楼花搜索引擎','',$visit->words)))) ?></td>
      <td><?php echo $visit->ip ?></td>
      <td><?php echo $visit->email ?></td>
    </tr>
<?php
  }
?>
  </tbody>
</table>


<hr size="5" color="blue">
<h2>
<?php esc_html_e('20大热门搜索页面', 'my-plugin-textdomain');
  $top_pages = $wpdb->get_results("select words, count(*) as ct from wp_statistics_search group by words order by 2 desc limit 20;");
?>
</h2>
<table class="table-style-two" id="top_pages_table">
  <thead>
    <tr>
      <th>搜索命中页面</th>
      <th>搜索次数</th>
    </tr>
  </thead>
  <tbody>
    <?php
      foreach($top_pages as $page){
?>
    <tr>
      <td><?php echo str_replace('"','',str_replace('~','',str_replace('-','',str_replace('多伦多最新最全的楼花搜索引擎','',$page->words)))) ?></td>
      <td><?php echo $page->ct ?></td>
    </tr>
<?php
  }
?>
  </tbody>
</table>


<hr size="5" color="blue">

<h2>
<?php esc_html_e('过去30天20大热门搜索页面', 'my-plugin-textdomain');
  $top_pages = $wpdb->get_results("select words, count(*) as ct from wp_statistics_search where last_counter>now()-interval 30 day group by words order by 2 desc limit 20;");
?>
</h2>
<table class="table-style-two" id="top_pages_table">
  <thead>
    <tr>
      <th>搜索命中页面</th>
      <th>搜索次数</th>
    </tr>
  </thead>
  <tbody>
    <?php
      foreach($top_pages as $page){
?>
    <tr>
      <td><?php echo str_replace('"','',str_replace('~','',str_replace('-','',str_replace('多伦多最新最全的楼花搜索引擎','',$page->words)))) ?></td>
      <td><?php echo $page->ct ?></td>
    </tr>
<?php
  }
?>
  </tbody>
</table>
<hr size="5" color="blue">

<h2>
<?php esc_html_e('过去1天20大热门搜索页面', 'my-plugin-textdomain');
  $top_pages = $wpdb->get_results("select words, count(*) as ct from wp_statistics_search where last_counter>now()-interval 2 day group by words order by 2 desc limit 20;");
?>
</h2>
<table class="table-style-two" id="top_pages_table">
  <thead>
    <tr>
      <th>搜索命中页面</th>
      <th>搜索次数</th>
    </tr>
  </thead>
  <tbody>
    <?php
      foreach($top_pages as $page){
?>
    <tr>
      <td><?php echo str_replace('"','',str_replace('~','',str_replace('-','',str_replace('多伦多最新最全的楼花搜索引擎','',$page->words)))) ?></td>
      <td><?php echo $page->ct ?></td>
    </tr>
<?php
  }
?>
  </tbody>
</table>
<hr size="5" color="blue">

<h2>
<?php esc_html_e('过去3天新出现的搜索页面', 'my-plugin-textdomain');
  $top_pages = $wpdb->get_results("select words, min(last_counter) as earliest_date, min(id) as min_id from wp_statistics_search group by words having min(last_counter)>now()-interval 3 day order by 3 desc;");
?>
</h2>
<table class="table-style-two" id="top_pages_table">
  <thead>
    <tr>
      <th>搜索命中页面</th>
      <th>首次被搜到日期</th>
    </tr>
  </thead>
  <tbody>
    <?php
      foreach($top_pages as $page){
?>
    <tr>
      <td><?php echo str_replace('"','',str_replace('~','',str_replace('-','',str_replace('多伦多最新最全的楼花搜索引擎','',$page->words)))) ?></td>
      <td><?php echo $page->earliest_date ?></td>
    </tr>
<?php
  }
?>
  </tbody>
</table>
<?php
}

function visited_page_analysis_contents() {
    global $wpdb;

?>
<style>
table.table-style-two {
        font-family: verdana, arial, sans-serif;
        font-size: 11px;
        color: #333333;
        border-width: 1px;
        border-color: #3A3A3A;
        border-collapse: collapse;
    }
 
    table.table-style-two th {
        border-width: 1px;
        padding: 8px;
        border-style: solid;
        border-color: #517994;
        background-color: #B2CFD8;
    }
 
    table.table-style-two tr:hover td {
        background-color: #DFEBF1;
    }
 
    table.table-style-two td {
        border-width: 1px;
        padding: 8px;
        border-style: solid;
        border-color: #517994;
        background-color: #ffffff;
    }
</style>

<h1>
<?php esc_html_e('页面访问分析', 'my-plugin-textdomain');
?>
</h1>
<h2>
<?php esc_html_e('最近三天访问最多页面', 'my-plugin-textdomain');
$top_pages = $wpdb->get_results("select page_id, page_name, count(*) as ct, count(distinct ip) as ip_ct from cc_view_visitors_property_history where logdate>now()-interval 3 day and page_name is not null and page_name<>'Home' group by page_id, page_name order by 4 desc limit 10;");
?>
</h2>
<table class="table-style-two" id="top_pages_table">
  <thead>
    <tr>
      <th>页面名称</th>
      <th>访问次数</th>
      <th>IP个数</th>
    </tr>
  </thead>
  <tbody>
<?php
  foreach($top_pages as $top_page){
?>
    <tr>
      <td><a href='comecondo.com/?p=<?php echo $top_page->page_id ?>'><?php echo $top_page->page_name ?></a></td>
      <td><?php echo $top_page->ct ?></td>
      <td><?php echo $top_page->ip_ct ?></td>
    </tr>
<?php
  }
?>
  </tbody>
</table>
<h2>
<?php esc_html_e('最近三天访问最多楼盘', 'my-plugin-textdomain');
$top_properties = $wpdb->get_results("select page_id, property_name, count(*) as ct, count(distinct ip) as ip_ct from cc_view_visitors_property_history where logdate>now()-interval 3 day and property_name is not null group by page_id, property_name order by 4 desc limit 20;");
?>
</h2>
<table class="table-style-two" id="top_pages_table">
  <thead>
    <tr>
      <th>楼盘名称</th>
      <th>访问次数</th>
      <th>IP个数</th>
    </tr>
  </thead>
  <tbody>
<?php
  foreach($top_properties as $top_property){
?>
    <tr>
      <td><a href='comecondo.com/?p=<?php echo $top_property->page_id ?>'><?php echo $top_property->property_name ?></a></td>
      <td><?php echo $top_property->ct ?></td>
      <td><?php echo $top_property->ip_ct ?></td>
    </tr>
<?php
  }
?>
  </tbody>
</table>
<h1>
<?php esc_html_e('最近三天页面访问记录', 'my-plugin-textdomain');
?>
</h1>
<button onclick='window.location.reload(true);'>刷新页面</button>
<?php
$pages = $wpdb->get_results("select concat(a.logdate, ' ', a.logtime) as logtime, a.ip, concat(c.city,'-',c.region,'-',c.country) as ip_location, submission, concat(b.firstname,' ',b.lastname) as name, agent, page_name, property_city from cc_view_visitors_property_history a left join cc_submission_details b on a.submission=b.email left join cc_ip_city c on a.ip=c.ip where a.logdate>now()-interval 3 day and page_name is not null and page_name<>'Home' order by 1 desc;");
?>
<script type="text/javascript">
function filterTable(col, row){
    table = document.getElementById("visits_table");
    rows  = table.getElementsByTagName("TR");
    val   = rows[row].getElementsByTagName("TD")[col-1].innerHTML.toLowerCase();
    /* window.alert(val); */
    
    new_table = document.createElement("table");
    new_table.appendChild(rows[0].cloneNode(true));
    for (i=1; i<rows.length; i++){
        if (rows[i].getElementsByTagName("TD")[col-1].innerHTML.toLowerCase() == val) {
            new_table.appendChild(rows[i].cloneNode(true));
        }
    }
    /* window.alert(new_table.getElementsByTagName("TR").length); */
    new_table.setAttribute("class", "table-style-two");
    new_table.setAttribute("id", "visits_table");
    table.parentNode.replaceChild(new_table, table);
}
</script>


<table class="table-style-two" id="visits_table">
    <thead>
    <tr>
        <th>Visit Time</th>
        <th>IP</th>
        <th>IP Location</th>
        <th>Email</th>
        <th>Name</th>
        <th>Agent</th>
        <th>Page</th>
        <th>City</th>
    </tr>
    </thead>
    <tbody>
<?php
$i = 0;
foreach ($pages as $p){
    $i++;
?>
<form action="submission_contact_details.php" method="post" target="_blank">
    <tr>
        <td><?php echo $p->logtime;?></td>
        <!-- <td id="ip_td" onclick="filterTable(2,$('#ip_td').text())"><?php echo $p->ip;?></td> -->
        <td onclick="filterTable(2, <?php echo $i; ?>)"><?php echo $p->ip;?></td>
        <td onclick="filterTable(3, <?php echo $i; ?>)"><?php echo $p->ip_location;?></td>
        <td onclick="filterTable(4, <?php echo $i; ?>)"><?php echo $p->submission;?></td>
        <td onclick="filterTable(5, <?php echo $i; ?>)"><?php echo $p->name;?></td>
        <td onclick="filterTable(6, <?php echo $i; ?>)"><?php echo $p->agent;?></td>
        <td onclick="filterTable(7, <?php echo $i; ?>)"><?php echo $p->page_name;?></td>
        <td onclick="filterTable(8, <?php echo $i; ?>)"><?php echo $p->property_city;?></td>
    </tr>
</form>
<?php
}
?>
    </tbody>
</table>
<?php
}




function register_my_plugin_scripts() {
wp_register_style( 'my-plugin', plugins_url( 'ddd/css/plugin.css' ) );
wp_register_script( 'my-plugin', plugins_url( 'ddd/js/plugin.js' ) );
}



add_action( 'admin_enqueue_scripts', 'register_my_plugin_scripts' );



function load_my_plugin_scripts( $hook ) {
// Load only on ?page=sample-page
if( $hook != 'toplevel_page_sample-page' ) {
return;
}

// Load style & scripts.
wp_enqueue_style( 'my-plugin' );
wp_enqueue_script( 'my-plugin' );
}

function listdir_by_date($path){

  $ar = [];
  chdir($path);
  array_multisort(array_map('filemtime', ($files = glob("*.*"))), SORT_DESC, $files);
  foreach($files as $filename)
  {
    $ar[] = $filename;
  }

  return $ar;
}

add_action( 'admin_enqueue_scripts', 'load_my_plugin_scripts' );