I have a problem with processing a file. First I give you a summary of the situation.
The idea is to receive in a csv the prices of 3 types of rooms for a specific date (one line for each day), and with that file I must insert them in the corresponding tables of the booking plugin that we have chosen (hbook).
This data will go to 5 tables:
- seasons: here are the seasons. Just save ID and name.
- season_dates: here the start and end dates of each season and the days of the week to which it applies are stored.
- rates: prices. The data that matters to us here is price and quantity ID.
- rates_season: price for each season. The id of the price and the one of the season is saved.
- rates_accom: price for each room.
First I tried to use transactions if at some point in the process something went wrong, leave the data as before starting everything but I do not remember why I had to discard it, now I am trying to mount temporary tables where to load the data and in the end if everything has gone well I delete the original tables and I rename the temporary ones with the original name.
The csv file that I have to process: link
The code:
class Debug{
public function Print_rr($flag = true){
if(_DEBUG_){
echo "<pre>".print_r($this, $flag)."</pre>";
}
}
public static function WriteLog($line, $msg = 'test'){
if(_DEBUG_){
$file = _PLUGIN_PATH_ . 'log/log.txt';
$errorMessage = "[".date("d-m-Y H:i:s")."] $line | ";
if(!is_object($msg) && !is_array($msg)){
$errorMessage .= $msg;
}
else{
$errorMessage .= print_r($msg, true);
}
error_log("$errorMessage\n", 3, $file);
}
}
}
function ProcessFileTari($tarifaPath){
$startTime = microtime(true);
global $wpdb;
try{
$wpdb->flush();
$ok = DebugQuery(__LINE__, "CREATE TABLE IF NOT EXISTS '"._SEASONS_TEMPTABLE_."' (
'id' bigint(20) NOT NULL AUTO_INCREMENT,
'name' varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;");
$ok = $ok && DebugQuery(__LINE__, "CREATE TABLE IF NOT EXISTS '"._SEASONS_DATES_TEMPTABLE_."' (
'id' bigint(20) NOT NULL AUTO_INCREMENT,
'season_id' bigint(20) NOT NULL,
'start_date' date NOT NULL,
'end_date' date NOT NULL,
'days' varchar(13) COLLATE utf8mb4_unicode_520_ci NOT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;");
$ok = $ok && DebugQuery(__LINE__, "CREATE TABLE IF NOT EXISTS '"._RATES_SEASONS_TEMPTABLE_."' (
'rate_id' bigint(20) NOT NULL,
'season_id' bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;");
$ok = $ok && DebugQuery(__LINE__, "CREATE TABLE IF NOT EXISTS '"._RATES_TEMPTABLE_."' (
'id' bigint(20) NOT NULL AUTO_INCREMENT,
'type' varchar(15) COLLATE utf8mb4_unicode_520_ci NOT NULL,
'all_accom' tinyint(1) NOT NULL,
'all_seasons' tinyint(1) NOT NULL,
'amount' decimal(14,2) NOT NULL,
'nights' smallint(6) NOT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;");
$ok = $ok && DebugQuery(__LINE__, "CREATE TABLE IF NOT EXISTS '"._RATES_ACCOM_TEMPTABLE_."' (
'rate_id' bigint(20) NOT NULL,
'accom_id' bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;");
if(!$ok){
Debug::WriteLog(__LINE__, "no ok creacion tablas temporales");
}
$ok = $ok && false !== DebugQuery(__LINE__, 'TRUNCATE '._SEASONS_TEMPTABLE_.';');
$ok = $ok && false !== DebugQuery(__LINE__, 'TRUNCATE '._SEASONS_DATES_TEMPTABLE_.';');
$ok = $ok && false !== DebugQuery(__LINE__, 'TRUNCATE '._RATES_SEASONS_TEMPTABLE_.';');
$ok = $ok && false !== DebugQuery(__LINE__, 'TRUNCATE '._RATES_TEMPTABLE_.';');
$ok = $ok && false !== DebugQuery(__LINE__, 'TRUNCATE '._RATES_ACCOM_TEMPTABLE_.';');
if(!$ok){
Debug::WriteLog(__LINE__, "no ok vaciado tablas originales");
}
$handle = fopen($tarifaPath, "r");
if ($handle) {
$prevLine = array('', 0, 0, 0);
$firstLoop = true;
$precioInd = 0; //individuales
$precioDtr = 0; //dobles
$precioTri = 0; //triples
$startDate = '';
$endDate = '';
$sql = "SELECT post_id, meta_value, (SELECT meta_value FROM wp_postmeta WHERE meta_key = 'ex_accomodation_supplement' AND post_id = pm.post_id) AS supplement";
$sql.= " FROM wp_postmeta pm";
$sql.= " WHERE meta_key = 'ex_accomodation_code'";
$sql.= " AND post_id IN (SELECT id";
$sql.= " FROM wp_posts";
$sql.= " WHERE post_type = 'hb_accommodation'";
$sql.= " AND post_status = 'publish');";
$accomCodesOArray = $wpdb->get_results($sql);
$accomCodes = array();
foreach($accomCodesOArray as $code){
$data = new stdClass();
$data->post_id = $code->post_id;
$data->supplement = $code->supplement != null ? $code->supplement : 0;
$accomCodes[$code->meta_value] = $data;
}
//Debug::WriteLog(__LINE__, print_r($accomCodes, true));
while (($line = fgets($handle)) !== false && $ok) {
$datos = explode(";", $line);
$precioInd = $datos[1]; //individuals
$precioDtr = $datos[2]; //dobles
$precioTri = $datos[3]; //triples
if($firstLoop){
$startDate = $datos[0];
$firstLoop = false;
}
else{
if($precioInd == $prevLine[1] && $precioDtr == $prevLine[2] && $precioTri == $prevLine[3]){
//same season
$endDate = $datos[0];
}
else{
//new season
$ok = $ok && InsertSeasonData($startDate, $endDate, $accomCodes, $precioInd, $precioDtr, $precioTri);
if(!$ok){
Debug::WriteLog(__LINE__, "no ok una de las season");
}
$startDate = $datos[0];
$endDate = $datos[0];
}
}
$prevLine = $datos;
}
$ok = $ok && InsertSeasonData($startDate, $endDate, $accomCodes, $precioInd, $precioDtr, $precioTri);
if($ok){
//$ok = $ok && DebugQuery(__LINE__, 'DROP TABLE ''._SEASONS_TABLE_.'';', true);
//$ok = $ok && DebugQuery(__LINE__, 'DROP TABLE ''._SEASONS_DATES_TABLE_.'';', true);
//$ok = $ok && DebugQuery(__LINE__, 'DROP TABLE ''._RATES_SEASONS_TABLE_.'';', true);
//$ok = $ok && DebugQuery(__LINE__, 'DROP TABLE ''._RATES_TABLE_.'';', true);
//$ok = $ok && DebugQuery(__LINE__, 'DROP TABLE ''._RATES_ACCOM_TABLE_.'';', true);
//$ok = $ok && DebugQuery(__LINE__, 'RENAME TABLE ''._SEASONS_TEMPTABLE_.'' TO ''._SEASONS_TABLE_.'';', true);
//$ok = $ok && DebugQuery(__LINE__, 'RENAME TABLE ''._SEASONS_DATES_TEMPTABLE_.'' TO ''._SEASONS_DATES_TABLE_.'';', true);
//$ok = $ok && DebugQuery(__LINE__, 'RENAME TABLE ''._RATES_SEASONS_TEMPTABLE_.'' TO ''._RATES_SEASONS_TABLE_.'';', true);
//$ok = $ok && DebugQuery(__LINE__, 'RENAME TABLE ''._RATES_TEMPTABLE_.'' TO ''._RATES_TABLE_.'';', true);
//$ok = $ok && DebugQuery(__LINE__, 'RENAME TABLE ''._RATES_ACCOM_TEMPTABLE_.'' TO ''._RATES_ACCOM_TABLE_.'';', true);
Debug::WriteLog(__LINE__, "Ahora tocaria borrar y renombrar las tablas");
}
else{
Debug::WriteLog(__LINE__, "no ok ultima season");
}
fclose($handle);
if(!_DEBUG_){
unlink($tarifaPath);
}
}
else{
update_option('hbook_ex_errorTarifaFile', json_encode($handle, JSON_UNESCAPED_UNICODE));
}
}
catch (Exception $e) {
Debug::WriteLog(__LINE__, 'Excepción capturada procesando tarifa.csv: '.print_r($e->getMessage(), true));
Debug::WriteLog(__LINE__, print_r($e, true));
}
Debug::WriteLog(__LINE__, "Elapsed time is: ". (microtime(true) - $startTime) ." seconds");
//test_transaction();
}
function InsertSeasonData($startDate, $endDate, $accomCodes, $precioInd, $precioDtr, $precioTri){
global $wpdb;
$wpdb->flush();
$ok = true;
$seasonName = str_replace("/", "", $startDate) . "-" . str_replace("/", "", $endDate);
$sql = "INSERT INTO " . _SEASONS_TEMPTABLE_ . " ('name') VALUES ('$seasonName');";
$ok = DebugQuery(__LINE__, $sql);
$seasonId = $wpdb->insert_id;
if($ok){
$startDateFormatted = DateTime::createFromFormat('d/m/Y', $startDate)->format('Y-m-d');
$endDateFormatted = DateTime::createFromFormat('d/m/Y', $endDate)->format('Y-m-d');
$sql = "INSERT INTO " . _SEASONS_DATES_TEMPTABLE_ . " ('season_id', 'start_date', 'end_date', 'days') ";
$sql.= "VALUES ($seasonId, '$startDateFormatted', '$endDateFormatted', '0,1,2,3,4,5,6');";
$ok = DebugQuery(__LINE__, $sql);
if($ok){
foreach($accomCodes as $key => $accom){
$price = 0;
switch($key){
case "IND":
$price = $precioInd + $accom->supplement;
break;
case "DAL":
case "DMA":
case "DMT":
$price = $precioDtr + $accom->supplement;
break;
case "DPT":
case "DTR":
$price = $precioTri + $accom->supplement;
break;
}
$sql ="INSERT INTO " . _RATES_TEMPTABLE_ . " ('type', 'all_accom', 'all_seasons', 'amount', 'nights') ";
$sql.="VALUES ('accom', 0, 0, $price, 1);";
$ok = DebugQuery(__LINE__, $sql);
$rateId = $wpdb->insert_id;
$sql ="INSERT INTO " . _RATES_SEASONS_TEMPTABLE_ . " ('rate_id', 'season_id') VALUES ($rateId, $seasonId);";
$ok = DebugQuery(__LINE__, $sql);
$sql ="INSERT INTO " . _RATES_ACCOM_TEMPTABLE_ . " ('rate_id', 'accom_id') VALUES ($rateId, {$accom->post_id})";
$ok = DebugQuery(__LINE__, $sql);
$tete = false;
}
}
}
return $ok;
}
function DebugQuery($line, $sql, $warnAdmin = false){
global $wpdb;
$wpdb->flush();
$ok = $wpdb->query($sql);
Debug::WriteLog($line, "Resultado: $ok - $sql");
if(!$ok){
Debug::WriteLog($line, $wpdb->last_error);
if($warnAdmin){
$to = "[email protected]";
$subject = "Error MySQL Hotel Acueducto";
Debug::WriteLog(__LINE__, "Mailing error to $to:" . wp_mail($to, $subject, $wpdb->last_error));
}
}
return $ok;
}
With the code like this, the last line in my log file is "536 | Now I would touch delete and rename the tables", that is, just what I expected.
But if I change the ending and decompose the drop and rename them, the last lines of the log are messy and of course, it gives bbdd errors:
if($ok){
$ok = $ok && DebugQuery(__LINE__, 'DROP TABLE ''._SEASONS_TABLE_.'';', true);
$ok = $ok && DebugQuery(__LINE__, 'DROP TABLE ''._SEASONS_DATES_TABLE_.'';', true);
$ok = $ok && DebugQuery(__LINE__, 'DROP TABLE ''._RATES_SEASONS_TABLE_.'';', true);
$ok = $ok && DebugQuery(__LINE__, 'DROP TABLE ''._RATES_TABLE_.'';', true);
$ok = $ok && DebugQuery(__LINE__, 'DROP TABLE ''._RATES_ACCOM_TABLE_.'';', true);
$ok = $ok && DebugQuery(__LINE__, 'RENAME TABLE ''._SEASONS_TEMPTABLE_.'' TO ''._SEASONS_TABLE_.'';', true);
$ok = $ok && DebugQuery(__LINE__, 'RENAME TABLE ''._SEASONS_DATES_TEMPTABLE_.'' TO ''._SEASONS_DATES_TABLE_.'';', true);
$ok = $ok && DebugQuery(__LINE__, 'RENAME TABLE ''._RATES_SEASONS_TEMPTABLE_.'' TO ''._RATES_SEASONS_TABLE_.'';', true);
$ok = $ok && DebugQuery(__LINE__, 'RENAME TABLE ''._RATES_TEMPTABLE_.'' TO ''._RATES_TABLE_.'';', true);
$ok = $ok && DebugQuery(__LINE__, 'RENAME TABLE ''._RATES_ACCOM_TEMPTABLE_.'' TO ''._RATES_ACCOM_TABLE_.'';', true);
//Debug::WriteLog(__LINE__, "Ahora tocaria borrar y renombrar las tablas");
}
Log (last lines):
[07-11-2017 08:01:22] 604 | Resultado: 1 - INSERT INTO wp_temp_rates_accom ('rate_id', 'accom_id') VALUES (2561, 560)
[07-11-2017 08:01:22] 604 | Resultado: 1 - INSERT INTO wp_temp_rates_accom ('rate_id', 'accom_id') VALUES (2562, 612)
[07-11-2017 08:01:22] 523 | Resultado: 1 - DROP TABLE 'wp_hb_seasons';
[07-11-2017 08:01:22] 597 | Resultado: 1 - INSERT INTO wp_temp_rates ('type', 'all_accom', 'all_seasons', 'amount', 'nights') VALUES ('accom', 0, 0, 90, 1);
[07-11-2017 08:01:22] 524 | Resultado: 1 - DROP TABLE 'wp_hb_seasons_dates';
[07-11-2017 08:01:22] 525 | Resultado: 1 - DROP TABLE 'wp_hb_rates_seasons';
[07-11-2017 08:01:22] 526 | Resultado: 1 - DROP TABLE 'wp_hb_rates';
[07-11-2017 08:01:22] 527 | Resultado: 1 - DROP TABLE 'wp_hb_rates_accom';
[07-11-2017 08:01:22] 601 | Resultado: 1 - INSERT INTO wp_temp_rates_seasons ('rate_id', 'season_id') VALUES (2563, 427);
[07-11-2017 08:01:22] 604 | Resultado: 1 - INSERT INTO wp_temp_rates_accom ('rate_id', 'accom_id') VALUES (2563, 563)
[07-11-2017 08:01:22] 597 | Resultado: 1 - INSERT INTO wp_temp_rates ('type', 'all_accom', 'all_seasons', 'amount', 'nights') VALUES ('accom', 0, 0, 85, 1);
[07-11-2017 08:01:22] 529 | Resultado: 0 - RENAME TABLE 'wp_temp_seasons' TO 'wp_hb_seasons';
[07-11-2017 08:01:22] 529 |
[07-11-2017 08:01:22] 601 | Resultado: 1 - INSERT INTO wp_temp_rates_seasons ('rate_id', 'season_id') VALUES (2564, 427);
[07-11-2017 08:01:22] 625 | Mailing error to [email protected]:
[07-11-2017 08:01:22] 553 | Elapsed time is: 8.24390721321 seconds
[07-11-2017 08:01:22] 604 | Resultado: 1 - INSERT INTO wp_temp_rates_accom ('rate_id', 'accom_id') VALUES (2564, 564)
[07-11-2017 08:01:22] 597 | Resultado: 1 - INSERT INTO wp_temp_rates ('type', 'all_accom', 'all_seasons', 'amount', 'nights') VALUES ('accom', 0, 0, 90, 1);
[07-11-2017 08:01:22] 601 | Resultado: 1 - INSERT INTO wp_temp_rates_seasons ('rate_id', 'season_id') VALUES (2565, 427);
[07-11-2017 08:01:22] 604 | Resultado: 1 - INSERT INTO wp_temp_rates_accom ('rate_id', 'accom_id') VALUES (2565, 565)
[07-11-2017 08:01:22] 597 | Resultado: 1 - INSERT INTO wp_temp_rates ('type', 'all_accom', 'all_seasons', 'amount', 'nights') VALUES ('accom', 0, 0, 45, 1);
[07-11-2017 08:01:22] 601 | Resultado: 1 - INSERT INTO wp_temp_rates_seasons ('rate_id', 'season_id') VALUES (2566, 427);
[07-11-2017 08:01:22] 604 | Resultado: 1 - INSERT INTO wp_temp_rates_accom ('rate_id', 'accom_id') VALUES (2566, 612)
[07-11-2017 08:01:22] 564 | Resultado: - INSERT INTO wp_temp_seasons ('name') VALUES ('31102018-01112018');
[07-11-2017 08:01:22] 564 | Table 'hotextra_segovia.wp_temp_seasons' doesn't exist
[07-11-2017 08:01:22] 510 | no ok una de las season
[07-11-2017 08:01:22] 537 | no ok ultima season
[07-11-2017 08:01:22] 553 | Elapsed time is: 7.65954709053 seconds
The select that fills the variable $ accomCodesOArray and that I use to fill the $ accomCodes array is correct and gives this result: 561 DMA 15 560 DAL null 563 DTR null 564 DMT null 565 DPT null 612 IND null
Thank you.