<?php
include_once "config.php";
require_once "lib/dbmysql.class.php";

error_reporting(E_ALL);

$db = new DbMySQL(DB_HOST, DB_NAME, DB_USER, DB_PWD);
$db->connect();

echo $sql = "SELECT count(*) as houses_count FROM realty WHERE url_path is null";
$houses_count = $db->select_row($sql);

echo "HOUSES for UPDATE:".$houses_count['houses_count']."\n";
$processing = 100;

echo "iterations_number=" . $iterations_number = ceil($houses_count['houses_count'] / $processing)."\n";
$i=0;

for ($iteration = 0; $iteration < $iterations_number + 1; $iteration++) {
    $sql = "SELECT * FROM realty Where url_path is null LIMIT " . $processing . " OFFSET " . ($iteration * $processing);
    echo $sql . "\n";
    $houses = $db->select($sql);
    echo "HOUSES".$iteration."-".count($houses)."\n";

    foreach ($houses as $key => $house) {
        $sql = "update realty set city='" . urldecode($house['city']) . "', address='" . urldecode($house['address']) . "' WHERE ID='" . $house['ID'] . "'";
        echo $sql . "\n";
        $db->query($sql);
        //SET - URL
        if ($house['address'] != '' && $house['state'] != '' && $house['zip'] != '' && $house['city'] != '') {
            $sql = "update realty set url_path='" . str_replace(" ", "-", preg_replace("|[^[:alnum:]\s]|", "", unhtmlspecialchars($house['address']))) . "-" . str_replace(" ", "-", preg_replace("|[^[:alnum:]\s]|", "", unhtmlspecialchars($house['city']))) . "-" . $house['state'] . "-" . $house['zip'] . "' where (url_path is null or url_path!='') and ID='" . $house['ID'] . "'";
            echo $sql . "\n";
            $db->query($sql);
        }
    }
    echo "updated realty \n";
}

//    preg_replace("|[^[:alnum:]\s]|", "", unhtmlspecialchars("16111 ST MARY&#039;S CHURCH TERRACE"));

    //assign cats
    //INSERT INTO content_category (tid,houses_id) (SELECT 1,houses_id FROM houses);
    //INSERT INTO content_category (tid,houses_id) (SELECT 17,houses_id FROM houses);
    //UPDATE houses SET tid=8;
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 1,\"ID\" FROM realty WHERE status='For Sale')";
//    $db->query($sql);

    //$sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 3,ID FROM realty WHERE status='For Sale' and status_type='')";
    //$db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 3,\"ID\" FROM realty WHERE status='For Sale' and status_type not in ('Short Sale','Foreclosure/Bank Owned','Foreclosure'))";
//    $db->query($sql);
//
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 2,\"ID\" FROM realty WHERE status='For Sale' and  status_type in ('Short Sale','Foreclosure/Bank Owned','Foreclosure'))";
//    $db->query($sql);
//
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 8,\"ID\" FROM realty WHERE status='For Sale' and  status_type in ('Short Sale','Foreclosure/Bank Owned','Foreclosure'))";
//    $db->query($sql);

    $sql = "UPDATE realty SET path1='listing' WHERE status='For Sale' and status_type not in ('Short Sale','Foreclosure/Bank Owned','Foreclosure')";
    echo $sql . "\n";
    $db->query($sql);

    $sql = "UPDATE realty SET path1='foreclosure' WHERE status='For Sale' and  status_type in ('Short Sale','Foreclosure/Bank Owned','Foreclosure')";
    echo $sql . "\n";
    $db->query($sql);

    $sql = "UPDATE realty SET path1='reo' WHERE status='For Sale' and  status_type in ('Foreclosure/Bank Owned')";
    echo $sql . "\n";
    $db->query($sql);

    $sql = "UPDATE realty SET path1='reo' WHERE  source LIKE '%IndyMac Bank%' or source LIKE '%Wells Fargo%'";
    echo $sql . "\n";
    $db->query($sql);

//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 5,ID FROM realty WHERE type LIKE '%condo%')";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 5,ID FROM realty WHERE type LIKE '%Condominium%')";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 5,ID FROM realty WHERE type LIKE '%CONDO/Town House%')";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 6,ID FROM realty WHERE type LIKE '%ranch%')";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 12,ID FROM realty WHERE type LIKE '%mobile%')";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 10,ID FROM realty WHERE type LIKE '%residential%')";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 7,ID FROM realty WHERE type LIKE '%land%')";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 13,ID FROM realty WHERE type LIKE '%Single Family%')";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 13,ID FROM realty WHERE type LIKE '%SFR%')";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 14,ID FROM realty WHERE type LIKE '%Multi Family%')";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 14,ID FROM realty WHERE type LIKE '%Multi-Family%')";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 15,ID FROM realty WHERE type LIKE '%Commercial%')";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 4,ID FROM realty WHERE price IS NOT NULL and price!='' AND REPLACE(REPLACE(price,'$',''),',','')<30000)";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 11,ID FROM realty WHERE price IS NOT NULL and price!='' AND REPLACE(REPLACE(price,'$',''),',','')>700000)";
//    $db->query($sql);
//    $sql = "INSERT IGNORE INTO content_category (tid,houses_id) (SELECT 9,ID FROM realty WHERE status='For Sale' and status_type not in ('Short Sale','Foreclosure/Bank Owned','Foreclosure'))";
//    $db->query($sql);

function unhtmlspecialchars($string)
{
    $string = str_replace('&amp;', '&', $string);
    $string = str_replace('&#039;', '\'', $string);
    $string = str_replace('&quot;', '\"', $string);
    $string = str_replace('&lt;', '<', $string);
    $string = str_replace('&gt;', '>', $string);
    $string = str_replace('#', '', $string);
    return $string;
}
?>