<?php
include "config.inc.php";
include "functions.php";
date_default_timezone_set("Europe/Dublin");
$conn = mysql_connect($MYSQL_SERVER, $MYSQL_USER, $MYSQL_PASSWORD);
mysql_select_db($MYSQL_DATABASE);
$languages = array();
$openFile = fopen("csv/lexvo.csv", "r");
if (!$openFile) {
echo "<p>File not found!</p>";
} else {
while ($values = fgetcsv($openFile, 2048, ";")) {
if (!isset($languages[$values[0]])) {
$languages[$values[0]] = array();
$languages[$values[0]]["iso1"] = $values[1];
$languages[$values[0]]["labels"] = array();
}
$languages[$values[0]]["labels"][] = trim($values[2]);
}
}
fclose($openFile);
foreach ($languages as $iso3 => $language_array) {
mysql_query("INSERT INTO language SET iso639p3 = '{$iso3}', iso639p1 = '" . $language_array["iso1"] . "', labels =" . db_prep(implode(";", $language_array["labels"]))) or die(mysql_error());
}
include "functions.php";
$openFile = fopen("csv/isco.csv", "r");
if (!$openFile) {
echo "<p>File not found!</p>";
} else {
$row = 0;
while ($values = fgetcsv($openFile, 2048, ";")) {
if ($row >= 0) {
$minor_code = NULL;
$submajor_code = NULL;
$major_code = NULL;
switch (strlen($values[0])) {
case 4:
$minor_code = substr($values[0], 0, 3);
$submajor_code = substr($values[0], 0, 2);
$major_code = substr($values[0], 0, 1);
break;
case 3:
$submajor_code = substr($values[0], 0, 2);
$major_code = substr($values[0], 0, 1);
break;
case 2:
$major_code = substr($values[0], 0, 1);
break;
}
$sql = mysql_query("INSERT INTO isco SET code = '{$values['0']}', name = '{$values['1']}', major_code =" . db_prep($major_code) . ", submajor_code =" . db_prep($submajor_code) . ", minor_code = " . db_prep($minor_code)) or die(mysql_error());
}
$row++;
}
}
fclose($openFile);
}
## INSERTING CONTACT AND AVOIDING SOME DUPLICATES ##
if ($contact != '' || $email != '' || $information != '' || $phone != '' || $fax != '') {
if ($email != '') {
$query = "SELECT id FROM contact WHERE email = '{$email}'";
} elseif ($information != '' && $phone != '' && $fax != '') {
$query = "SELECT id FROM contact WHERE information LIKE '{$information}' AND fax LIKE '{$fax}' AND phone LIKE '{$phone}' AND country_id = '{$address_array['country_id']}'";
} elseif ($information != '' && $phone != '') {
$query = "SELECT id FROM contact WHERE information LIKE '{$information}' AND phone LIKE '{$phone}' AND country_id = '{$address_array['country_id']}'";
} elseif ($information != '' && $fax != '') {
$query = "SELECT id FROM contact WHERE information LIKE '{$information}' AND fax LIKE '{$fax}' AND country_id = '{$address_array['country_id']}'";
} elseif ($phone != '' && $fax != '') {
$query = "SELECT id FROM contact WHERE phone LIKE '{$phone}' AND fax LIKE '{$fax}' AND country_id = '{$address_array['country_id']}'";
} else {
$query = "SELECT id FROM contact WHERE email = '{$email}' AND information LIKE '{$information}' AND phone LIKE '{$phone}' AND fax LIKE '{$fax}' AND country_id = '{$address_array['country_id']}'";
}
$contact_id = select_id($query);
if ($contact_id == '') {
mysql_query("INSERT INTO contact SET \n\t\t\t\t\temployer_id = '{$employer_id}',\n\t\t\t\t\tcontact = '{$contact}',\n\t\t\t\t\tinformation = '{$information}',\n\t\t\t\t\tphone = '{$phone}',\n\t\t\t\t\temail='{$email}',\n\t\t\t\t\tfax = '{$fax}',\n\t\t\t\t\tcountry_id ='{$address_array['country_id']}',\t\t\t\t\t\t\n\t\t\t\t\turl = '{$url}',\n\t\t\t\t\tscraper_date = SYSDATE(),\t \n\t\t\t\t\tscraper_hour = SYSDATE()") or die(mysql_error());
$contact_id = select_id("SELECT LAST_INSERT_ID() FROM contact");
}
}
## UPDATING JOB ##
mysql_query("UPDATE job SET \n\t\t\temployer_id = " . db_prep($employer_id) . ",\n\t\t\tcontact_id = " . db_prep($contact_id) . ", \n\t\t\ttitle_id = " . db_prep($title_id) . ", \n\t\t\tstarting_date = STR_TO_DATE('{$starting_date}', '%d/%m/%Y'),\n\t\t\tending_date = STR_TO_DATE('{$ending_date}', '%d/%m/%Y'),\n\t\t\tcountry_id = " . db_prep($country_id) . ", \n\t\t\tregion_id = " . db_prep($region_id) . ", \n\t\t\tminimum_salary = " . db_prep($minimum_salary) . ", \n\t\t\tmaximum_salary = " . db_prep($maximum_salary) . ", \n\t\t\tsalary_currency_id = " . db_prep($salary_currency_id) . ", \n\t\t\tsalary_tax_id = " . db_prep($salary_tax_id) . ", \n\t\t\tsalary_period_id = " . db_prep($salary_period_id) . ", \n\t\t\thours_per_week = " . db_prep($hours_per_week) . ", \n\t\t\tcontract_type_id = " . db_prep($contract_type_id) . ", \n\t\t\tcontract_hours_id = " . db_prep($contract_hours_id) . ", \n\t\t\taccommodation_provided = " . db_prep($accommodation_provided) . ", \n\t\t\trelocation_covered = " . db_prep($relocation_covered) . ", \n\t\t\tmeals_included = " . db_prep($meals_included) . ", \n\t\t\ttravel_expenses = " . db_prep($travel_expenses) . ", \n\t\t\teducation_skills_id = " . db_prep($education_skills_id) . ", \n\t\t\tprofessional_qualifications_required = " . db_prep($professional_qualifications_required) . ", \n\t\t\texperience_id = " . db_prep($experience_id) . ", \n\t\t\tdriving_license_id = " . db_prep($driving_license_id) . ", \n\t\t\tminimum_age = " . db_prep($minimum_age) . ", \n\t\t\tmaximum_age = " . db_prep($maximum_age) . ", \n\t\t\thow_to_apply_id = " . db_prep($how_to_apply_id) . ", \n\t\t\tlast_date_for_application = STR_TO_DATE('{$last_date_for_application}', '%d/%m/%Y'),\n\t\t\tdate_published = STR_TO_DATE('{$date_published}', '%d/%m/%Y'),\n\t\t\tlast_modification_date = STR_TO_DATE('{$last_modification_date}', '%d/%m/%Y'),\n\t\t\tnace_code = " . db_prep($nace_code) . ", \n\t\t\tnational_reference = " . db_prep($national_reference) . ", \n\t\t\teures_reference = " . db_prep($eures_reference) . ", \n\t\t\tisco_code = " . db_prep($isco_code) . ", \n\t\t\tisco_unit_code = " . db_prep($isco_unit_code) . ", \n\t\t\tisco_minor_code = " . db_prep($isco_minor_code) . ", \n\t\t\tisco_submajor_code = " . db_prep($isco_submajor_code) . ", \n\t\t\tisco_major_code = " . db_prep($isco_major_code) . ", \n\t\t\tnumber_of_posts = " . db_prep($number_of_posts) . ", \n\t\t\tjob_scraper_date = SYSDATE(),\t \n\t\t\tjob_scraper_hour = SYSDATE()\n\t\t\n\t\tWHERE url = '{$url}'") or die(mysql_error());
## WAITING AND CLEARING MEMORY ##
if ($cont_job > 1) {
sleep(0.5);
unset($title, $required_languages, $starting_date, $ending_date, $country, $region, $minimum_salary, $maximum_salary, $salary_currency, $salary_tax, $salary_period, $hours_per_week, $contract, $contract_type, $contract_hours, $accommodation_provided, $relocation_covered, $meals_included, $travel_expenses, $education_skills_required, $professional_qualifications_required, $experience_required, $driving_license_required, $minimum_age, $maximum_age, $name, $information, $address, $phone, $email, $fax, $how_to_apply, $contact, $last_date_for_application, $date_published, $national_reference, $last_modification_date, $nace_code, $isco_code, $isco_unit_code, $isco_minor_code, $isco_submajor_code, $isco_major_code, $number_of_posts, $other_value, $eures_reference, $contract_type_id, $contract_hours_id, $education_skills_id, $experience_id, $driving_license_id, $contact_id, $employer_id, $address_array, $how_to_apply_id, $title_id, $homepage, $dom, $text, $data, $sql, $query, $row, $salary_currency_id, $salary_period_id, $salary_tax_id, $salary, $salary['amount']);
}
}
mysql_query("INSERT INTO update_service SET date = SYSDATE(), hour = SYSDATE(), type='job'");
$query = str_replace(" information = NULL ", " information is NULL ", $query);
$contact_id = select_id($query);
if (!$contact_id) {
if (strlen($email) == 0 && isset($fax_array["email"])) {
$email = $fax_array["email"];
}
mysql_query("INSERT INTO contact SET\n employer_id = " . db_prep($employer_id) . ",\n information = " . db_prep($information) . ",\n country_code =" . db_prep($country_code) . ",\n email=" . db_prep($email) . ",\n fax = " . db_prep($fax_array['fax']) . ",\n url = '{$url}',\n scraper_date = SYSDATE(),\n scraper_hour = SYSDATE()");
$contact_id = select_id($query);
}
if ($contact_id) {
if ($phone != '') {
format_phone($phone);
}
}
}
if (!mysql_query("INSERT INTO job SET\n url = " . db_prep($url) . ",\n description = " . db_prep($description) . ",\n employer_id = " . db_prep($employer_id) . ",\n contact_id = " . db_prep($contact_id) . ",\n unique_id = '{$unique_id}',\n source_id = '{$source_id}',\n url_search = " . db_prep($url_search) . ",\n url_scraper_date = " . db_prep($url_scraper_date) . ",\n url_scraper_hour = " . db_prep($url_scraper_hour) . ",\n title_id = " . db_prep($title_id) . ",\n starting_date = STR_TO_DATE('{$starting_date}', '%d/%m/%Y'),\n ending_date = STR_TO_DATE('{$ending_date}', '%d/%m/%Y'),\n country_code = " . db_prep($country_code) . ",\n region_id = " . db_prep($region_id) . ",\n minimum_salary = " . db_prep($minimum_salary_array['amount']) . ",\n maximum_salary = " . db_prep($maximum_salary_array['amount']) . ",\n salary_comment = " . db_prep($salary_comment) . ",\n salary_currency_id = " . db_prep($salary_currency_id) . ",\n salary_tax_id = " . db_prep($salary_tax_id) . ",\n salary_period_id = " . db_prep($salary_period_id) . ",\n hours_per_week_min = " . db_prep($hours_per_week_array['min']) . ",\n hours_per_week_max = " . db_prep($hours_per_week_array['max']) . ",\n contract_type_id = " . db_prep($contract_type_id) . ",\n contract_hours_id = " . db_prep($contract_hours_id) . ",\n accommodation_provided = " . db_prep($accommodation_provided) . ",\n relocation_covered = " . db_prep($relocation_covered) . ",\n meals_included = " . db_prep($meals_included) . ",\n travel_expenses = " . db_prep($travel_expenses) . ",\n education_skills_id = " . db_prep($education_skills_id) . ",\n professional_qualifications_required = " . db_prep($professional_qualifications_required) . ",\n experience_id = " . db_prep($experience_id) . ",\n driving_license_id = " . db_prep($driving_license_id) . ",\n minimum_age = " . db_prep($minimum_age) . ",\n maximum_age = " . db_prep($maximum_age) . ",\n how_to_apply_id = " . db_prep($how_to_apply_id) . ",\n last_date_for_application = STR_TO_DATE('{$last_date_for_application}', '%d/%m/%Y'),\n date_published = STR_TO_DATE('{$date_published}', '%d/%m/%Y'),\n last_modification_date = STR_TO_DATE('{$last_modification_date}', '%d/%m/%Y'),\n nace_code = " . db_prep($nace_code) . ",\n national_reference = " . db_prep($national_reference) . ",\n eures_reference = " . db_prep($eures_reference) . ",\n isco_code = " . db_prep($isco_code) . ",\n number_of_posts = " . db_prep($number_of_posts) . ",\n job_scraper_date = SYSDATE(),\n job_scraper_hour = SYSDATE()")) {
write_log("not_imported_job.logs", array($unique_id, $source_id, mysql_error()));
} else {
$job_id = select_id("SELECT id FROM job where unique_id = '{$unique_id}' AND source_id = '{$source_id}'");
if ($required_languages) {
split_required_languages($required_languages);
}
}
}
}
}
closedir($handle);
} else {
die("Could not open directory: " . $dir);
}
} else {
function insert_name($table, $value)
{
$lowercase_value = db_prep(strtolower($value));
if ($value != '' && $value != '0' && $value != '.' && $value != '**') {
$query = "SELECT id FROM {$table} WHERE LOWER(name) = {$lowercase_value}";
$sql = mysql_query($query) or die($query . PHP_EOL . "caused: " . mysql_error());
if (mysql_num_rows($sql) == 0) {
mysql_query("INSERT INTO " . $table . " SET name =" . db_prep($value));
$sql = mysql_query($query) or die($query . PHP_EOL . "caused: " . mysql_error());
}
$row = mysql_fetch_object($sql);
return $row->id;
}
// TODO throw exception & log
}
