<?php
function osc_genRandomPassword($length = 8) {
    $dict = array_merge(range('a', 'z'), range('0', '9'), range('A', 'Z'));
    shuffle($dict);
    $pass = '';
    for($i = 0; $i < $length; $i++)
        $pass .= $dict[rand(0, count($dict) - 1)];
    return $pass;
}
date_default_timezone_set('Europe/Zagreb');
function get_username($userid) {
    $con = mysqli_connect("localhost","osclass","osclass","osclass");
    $username = mysqli_query($con,"SELECT s_username FROM zo_t_user WHERE pk_i_id = ".$userid."");
    return $username;
}
function get_email($userid) {
    $con = mysqli_connect("localhost","osclass","osclass","osclass");
    $email = mysqli_query($con,"SELECT s_email FROM zo_t_user WHERE pk_i_id = ".$userid."");
    return $email;
}

$xml_url = "//localhost/test_osclass.xml";
$xml = simplexml_load_file($xml_url);

$con = mysqli_connect("localhost","osclass","osclass","osclass");

foreach ($xml->listing as $listing)  {
    // t_item
    echo $listing->price;
    $user     = (string) $listing->user_id;
    $secret   = osc_genRandomPassword($length = 8);
    $date     = date('m/d/Y h:i:s a', time());
    $price    = (string) $listing->price;
    $currency = (string) $listing->currency;
    $username = get_username($user);
    $email    = get_email($user);
    $cat      = (string) $listing->category_id;
    mysqli_query($con, "INSERT INTO zo_t_item (`fk_i_user_id`, `fk_i_category_id`, `dt_pub_date`, `i_price`, `fk_c_currency_code`, `b_enabled`, `b_active`, `s_secret`, `b_show_email`, `dt_expiration`, `s_contact_name`, `s_contact_email`) VALUES (`".$user."`, `".$cat."`, `".$date."`, `".$price."`, `".$currency."`, `1`, `1`, `".$secret."`, `1`, `9999-12-31 23:59:59`, `".$username."`, `".$email."`)");

    // t_item_description
    $ad       = mysqli_query($con, "SELECT fk_i_item_id FROM zo_t_item WHERE `fk_i_user_id` = '{$user}' AND `fk_i_category_id` = '{$cat}' AND `dt_pub_date` = '{$date}' AND `i_price` = '{$price}' AND `s_secret` = '{$secret}')");
    $title    = (string) $listing->title;
    $desc     = (string) $listing->content;
    $loc      = "hr_HR";
    mysqli_query($con, "INSERT INTO zo_t_item_description (`fk_i_item_id`, `fk_c_locale_code`, `s_title`, `s_description`) VALUES ('{$ad}', '{$loc}', '{$title}', '{$desc}'");

    // t_item_location
    $ad       = mysqli_query($con, "SELECT fk_i_item_id FROM zo_t_item WHERE `fk_i_user_id` = '{$user}' AND `fk_i_category_id` = '{$cat}' AND `dt_pub_date` = '{$date}' AND `i_price` = '{$price}' AND `s_secret` = '{$secret}')");
    $country  = (string) $listing->country;
    $ccode    = "HR";
    $region   = (string) $listing->region;
    $city     = (string) $listing->city;
    $regionid = mysqli_query($con,"SELECT pk_i_id FROM zo_t_region WHERE s_name = ".$region."");
    $cityid   = mysqli_query($con,"SELECT pk_i_id FROM zo_t_city WHERE s_name = ".$city."");
    mysqli_query($con, "INSERT INTO zo_t_item_location (`fk_i_item_id`, `fk_c_country_code`, `s_country`, `s_region`, `fk_i_region_id`, `s_city`, `fk_i_city_id`) VALUES ('{$ad}', '{$ccode}', '{$country}', '{$region}, '{$regionid}', '{$city}', '{$cityid}'");
}