<?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 }