<?php
header('Content-type: text/xml');
$host = "d58093.mysql.zone.ee";
$user = "d58093sa114985";
$pass = "";
$database = "d58093_bredencom";
$con = mysqli_connect($host, $user, $pass, $database);
if(mysqli_connect_errno()){
	trigger_error('Database connection failed: '  . mysqli_connect_error(), E_USER_ERROR);
}
mysqli_set_charset($con, 'utf8');


////////////////////////
//SUURUSTE SELEKTEERIMINE
////////////////////////


//Valime laost kõik tooted, mille laoseis on suurem, kui 0
$queryID_s1 = 'SELECT product_id, otp_id, model, quantity, price, special, extra, extra2 FROM jk2031_otp_data GROUP BY product_id DESC';

//Valime tooted, mis vastavad 50/56 suurusele ehk 0-1 kuud; 0-1 aastat; 35/38; 39/42 ja otsime suuruse ID'de põhiselt (76, 75, 81, 123)
//$queryID_s1 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (76, 75, 81, 123) ORDER BY product_id ASC ';

//Valime tooted, mis vastavad 62/68
$queryID_s2 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (105, 117,81, 82, 75, 76, 116, 77, 109) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 74/80
$queryID_s3 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (99, 118, 81, 77, 109, 110, 78) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 86/92
$queryID_s4 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (100, 82, 454, 121, 110, 78, 111, 79) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 98/104
$queryID_s5 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (101, 82, 83, 454, 121, 78,111,79,112) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 110/116
$queryID_s6 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (102, 83, 454, 121, 78, 111, 79, 112) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 122/128
$queryID_s7 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (103, 83, 120, 454, 121, 79, 112, 80, 113) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 134/140
$queryID_s8 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (104, 120, 454,121, 79, 112, 80, 113) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 146/152
$queryID_s9 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (459, 468, 79, 112, 80) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 158/164
$queryID_s10 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (460, 468, 79, 80, 534) ORDER BY product_id DESC';

//Valime tooted, mis vastavad S
$queryID_s11 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (468, 120, 79, 80, 113, 534) ORDER BY product_id DESC';

//Valime tooted, mis vastavad M
$queryID_s12 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (469, 120, 79, 80, 113, 534) ORDER BY product_id DESC';

//Valime tooted, mis vastavad L
$queryID_s13 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (470, 120, 113, 534) ORDER BY product_id DESC';

//Valime tooted, mis vastavad XL
$queryID_s14 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (471, 120, 86, 113, 534) ORDER BY product_id DESC';


////////////////////////
//VANUSTE SELEKTEERIMINE
////////////////////////

//Valime tooted, mis vastavad 0-2 kuud
$queryID_v1 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (123, 117, 81, 82, 75, 76, 105) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 2-6 kuud
$queryID_v2 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (117, 118, 81, 82, 76, 116, 77, 109, 110, 99, 100, 101) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 6-12 kuud
$queryID_v3 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (117, 118, 81, 82, 77, 109, 110, 99, 100, 101) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 1-2 aastat
$queryID_v4 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (81, 82, 454, 121, 110, 78, 111, 79, 99, 100, 101) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 2-3 aastat
$queryID_v5 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (82, 83, 454, 121, 110, 78, 111, 79, 112, 100, 101) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 3-4 aastat
$queryID_v5 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (83, 454, 121, 111, 79, 112, 101, 102) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 4-5 aastat
$queryID_v6 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN 83, 454, 121, 79, 112, 101, 102) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 5-6 aastat
$queryID_v7 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (83, 454, 121, 79, 112, 102, 103) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 6-8 aastat
$queryID_v8 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (83, 129, 454, 121, 79, 112, 86, 113, 102, 103, 104) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 8-10 aastat
$queryID_v9 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (120, 454, 121, 79, 86, 113, 103, 104, 459) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 10-12 aastat
$queryID_v10 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (120, 79, 86, 113, 104, 459) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 12+ aastat
$queryID_v11 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (120, 79, 86, 113, 534, 439, 460, 468, 469) ORDER BY product_id DESC';

//Valime tooted, mis vastavad täiskasvanud
$queryID_v12 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (120, 79, 86, 113, 534, 460, 470, 469, 468, 471) ORDER BY product_id DESC';



/////// PEAÜMBERMÕÕDU SEKTSIOON

//Valime tooted, mis vastavad 35/38
//$queryID = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (76, 75, 81, 123) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 39/42
//$queryID = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (76, 75, 81, 123) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 43/46
//$queryID = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (76, 75, 81, 123) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 47/50
//$queryID = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (76, 75, 81, 123) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 51/54
//$queryID = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (76, 75, 81, 123) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 55/58
//$queryID = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (76, 75, 81, 123) ORDER BY product_id DESC';

//Valime tooted, mis vastavad 59/62
//$queryID = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (76, 75, 81, 123) ORDER BY product_id DESC';


$resultID_s1 = mysqli_query($con, $queryID_s1);
$resultID_s2 = mysqli_query($con, $queryID_s2);
$resultID_s3 = mysqli_query($con, $queryID_s3);
$resultID_s4 = mysqli_query($con, $queryID_s4);
$resultID_s5 = mysqli_query($con, $queryID_s5);
$resultID_s6 = mysqli_query($con, $queryID_s6);
$resultID_s7 = mysqli_query($con, $queryID_s7);
$resultID_s8 = mysqli_query($con, $queryID_s8);
$resultID_s9 = mysqli_query($con, $queryID_s9);
$resultID_s10 = mysqli_query($con, $queryID_s10);
$resultID_s11 = mysqli_query($con, $queryID_s11);
$resultID_s12 = mysqli_query($con, $queryID_s12);
$resultID_s13 = mysqli_query($con, $queryID_s13);
$resultID_s14 = mysqli_query($con, $queryID_s14);

$resultID_v1 = mysqli_query($con, $queryID_v1);
$resultID_v2 = mysqli_query($con, $queryID_v2);
$resultID_v3 = mysqli_query($con, $queryID_v3);
$resultID_v4 = mysqli_query($con, $queryID_v4);
$resultID_v5 = mysqli_query($con, $queryID_v5);
$resultID_v6 = mysqli_query($con, $queryID_v6);
$resultID_v7 = mysqli_query($con, $queryID_v7);
$resultID_v8 = mysqli_query($con, $queryID_v8);
$resultID_v9 = mysqli_query($con, $queryID_v9);
$resultID_v10 = mysqli_query($con, $queryID_v10);
$resultID_v11 = mysqli_query($con, $queryID_v11);
$resultID_v12 = mysqli_query($con, $queryID_v12);

echo mysqli_error($con);

//////////////////////////////////////////////////
//////////// S1 START ////////////////////////////
//////////////////////////////////////////////////


$xml_output_s1	 = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
$xml_output_s1 .= '<root>
';
$xml_output_s1 .= "<products>\n";

while ($row_s1 = mysqli_fetch_array($resultID_s1)){

// Pärime suuruste & omaduste unikaalse ID, millega pärime hiljem suuruste järgi laoseisud.
//$size_option_id_s1 = mysqli_fetch_array(mysqli_query($con, 'SELECT id FROM jk2031_otp_option_value WHERE product_id = "'.$row_s1['product_id'].'"'))['id'];

// Pärime esmalt kuvatavatele toodetele juurde teisest tabelist laoseisud, et tulevikus süsteem välistaks tooteid, mis pole laos
//$quantity_s1 = mysqli_fetch_array(mysqli_query($con, 'SELECT quantity FROM jk2031_otp_data WHERE otp_id = "'.$size_option_id_s1.'" AND product_id = "'.$row_s1['product_id'].'"'))['quantity'];


// Pärime tootele ID numbri
$product_id_s1 = mysqli_fetch_array(mysqli_query($con, 'SELECT product_id FROM jk2031_otp_data WHERE otp_id = "'.$row_s1['otp_id'].'"'))['product_id'];


// Pärime esmalt toodetele juurde eestikeelsed toote nimetused
$product_name_s1 = mysqli_fetch_array(mysqli_query($con, 'SELECT meta_title FROM jk2031_product_description WHERE product_id = "'.$product_id_s1.'"'))['meta_title'];

// Pärime tootega seotud suuruse ID
$product_size_id = mysqli_fetch_array(mysqli_query($con, 'SELECT child_option_value_id FROM jk2031_otp_option_value WHERE product_id = "'.$product_id_s1.'"'))['child_option_value_id'];



//$lisaPildid = mysqli_query($con, 'SELECT child_option_value_id FROM jk2031_otp_option_value WHERE product_id = "'.$product_id_s1.'"');

$otp_Options = mysqli_query($con, 'SELECT otp_id FROM jk2031_otp_data WHERE product_id = "'.$row_s1['product_id'].'" ORDER BY otp_id ASC');

$otp_Values = mysqli_query($con, 'SELECT child_option_value_id FROM jk2031_otp_option_value WHERE product_id = "'.$row_s1['product_id'].'"');





//$queryID_s1 = 'SELECT product_id FROM jk2031_otp_option_value WHERE child_option_value_id IN (76, 75, 81, 123) ORDER BY product_id ASC ';


$xml_output_s1 .= '<product>';
$xml_output_s1 .= "\n";

$xml_output_s1 .= ' <product_id>'.$row_s1['product_id'].'</product_id>';
$xml_output_s1 .= ' <opt_id>'.$row_s1['otp_id'].'</opt_id>';
$xml_output_s1 .= ' <ean>'.$row_s1['model'].'</ean>';
$xml_output_s1 .= ' <name>'.$row_s1['extra2'].'</name>';


	$xml_output_s1 .= '<sizes>';
	$xml_output_s1 .= "\n";

	while($option_id = mysqli_fetch_array($otp_Options)){


		// Pärime tootega seotud suuruse nime
		$product_size_value = mysqli_fetch_array(mysqli_query($con, 'SELECT name FROM jk2031_option_value_description WHERE language_id = 1 AND option_value_id = "'.$option_id['child_option_value_id'].'"'))['name'];



		// Pärida otp_id
		$size_otp_id = mysqli_fetch_array(mysqli_query($con, 'SELECT otp_id FROM jk2031_otp_data WHERE product_id = "'.$row_s1['product_id'].'" AND weight = "'.$product_size_value.'"'))['otp_id'];




		// Pärime laoseisu
		$product_qty = mysqli_fetch_array(mysqli_query($con, 'SELECT quantity FROM jk2031_otp_data WHERE otp_id = "'.$option_id['otp_id'].'"'))['quantity'];


		$xml_output_s1 .= '<size otp_id="'.$option_id['otp_id'].'" product_id="'.$product_id_s1.'" 	size_value_id="'.$option_id['child_option_value_id'].'" size_value_name="'.$product_size_value.'" qty="'.$product_qty.'">'.$product_size_value.'</size>';

	}

	$xml_output_s1 .= '</sizes>';



//$xml_output_s1 .= ' <filter_size>50/56</filter_size>';
//$xml_output_s1 .= ' <filter_size_id>144</filter_size_id>';
//$xml_output_s1 .= ' <filter_size_value_id>'.$size_option_id_s1.'</filter_size_value_id>';


//$xml_output_s1 .= ' <quantity>'.$quantity_s1.'</quantity>';

$xml_output_s1 .= "\n\t</product>\n";
}
$xml_output_s1 .= "</products></root>";

$file_s1 = 'suurus_s1.xml';
file_put_contents($file_s1, $xml_output_s1);
echo $xml_output_s1;

//////////////////////////////////////////////////
//////////// S1 LÕPP /////////////////////////////
//////////////////////////////////////////////////





?>