<?php
ob_start();
error_reporting(0);

$path = "../../";
include("../../map.php");

function xlsx_xml($value)
{
	$value = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F]/', '', (string)$value);
	$flags = (defined('ENT_XML1') ? ENT_XML1 : 0) | ENT_COMPAT;
	return htmlspecialchars($value, $flags, 'UTF-8');
}

function xlsx_col_name($index)
{
	$name = '';
	while ($index >= 0) {
		$name = chr($index % 26 + 65) . $name;
		$index = floor($index / 26) - 1;
	}
	return $name;
}

function xlsx_sheet_name($name)
{
	$name = preg_replace('/[\[\]\:\*\?\/\\\\]/', ' ', (string)$name);
	$name = trim($name);
	if ($name === '') {
		$name = 'Sayfa1';
	}
	return substr($name, 0, 31);
}

function xlsx_sheet_xml($rows, $stringMap)
{
	$xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
	$xml .= '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">';
	$xml .= '<sheetViews><sheetView workbookViewId="0"/></sheetViews><sheetFormatPr defaultRowHeight="15"/><sheetData>';

	foreach ($rows as $rowIndex => $row) {
		$r = $rowIndex + 1;
		$xml .= '<row r="' . $r . '">';
		foreach ($row as $colIndex => $value) {
			$cell = xlsx_col_name($colIndex) . $r;
			$key = trim((string)$value);
			$xml .= '<c r="' . $cell . '" t="s"><v>' . $stringMap[$key] . '</v></c>';
		}
		$xml .= '</row>';
	}

	$xml .= '</sheetData><pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/></worksheet>';
	return $xml;
}

function xlsx_shared_strings_xml($strings, $count)
{
	$xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
	$xml .= '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="' . intval($count) . '" uniqueCount="' . count($strings) . '">';
	foreach ($strings as $string) {
		$xml .= '<si><t>' . xlsx_xml($string) . '</t></si>';
	}
	$xml .= '</sst>';
	return $xml;
}

function exportExcel($filename = 'ExportExcel', $columns = array(), $data = array(), $replaceDotCol = array())
{
	if (!class_exists('ZipArchive')) {
		while (ob_get_level()) {
			ob_end_clean();
		}
		header('Content-Type: text/plain; charset=UTF-8');
		http_response_code(500);
		echo 'XLSX export icin PHP ZipArchive eklentisi gerekli.';
		exit;
	}

	$rows = array();
	$rows[] = array_values($columns);

	foreach ($data as $line) {
		$row = array();
		foreach ($columns as $i => $column) {
			$value = isset($line[$i]) ? $line[$i] : '';
			if (in_array($i, $replaceDotCol)) {
				$value = str_replace('.', ',', $value);
			}
			$row[] = $value;
		}
		$rows[] = $row;
	}

	$strings = array();
	$stringMap = array();
	$stringCount = 0;
	foreach ($rows as $row) {
		foreach ($row as $value) {
			$key = trim((string)$value);
			if (!array_key_exists($key, $stringMap)) {
				$stringMap[$key] = count($strings);
				$strings[] = $key;
			}
			$stringCount++;
		}
	}

	$sheetName = xlsx_sheet_name('Sayfa1');
	$file = preg_replace('/[^a-zA-Z0-9_\-]/', '_', $filename) . '_' . date('dmyHis') . '.xlsx';
	$tmp = tempnam(sys_get_temp_dir(), 'xlsx_');

	$zip = new ZipArchive();
	if ($zip->open($tmp, ZipArchive::CREATE | ZipArchive::OVERWRITE) !== true) {
		while (ob_get_level()) {
			ob_end_clean();
		}
		header('Content-Type: text/plain; charset=UTF-8');
		http_response_code(500);
		echo 'XLSX dosyasi olusturulamadi.';
		exit;
	}
	$zip->addFromString('[Content_Types].xml', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/><Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/><Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/><Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/><Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/></Types>');
	$zip->addFromString('_rels/.rels', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/></Relationships>');
	$zip->addFromString('docProps/app.xml', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><Application>V3Medya</Application></Properties>');
	$zip->addFromString('docProps/core.xml', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><dc:creator>V3Medya</dc:creator><cp:lastModifiedBy>V3Medya</cp:lastModifiedBy><dcterms:created xsi:type="dcterms:W3CDTF">' . gmdate('Y-m-d\TH:i:s\Z') . '</dcterms:created><dcterms:modified xsi:type="dcterms:W3CDTF">' . gmdate('Y-m-d\TH:i:s\Z') . '</dcterms:modified></cp:coreProperties>');
	$zip->addFromString('xl/workbook.xml', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="' . xlsx_xml($sheetName) . '" sheetId="1" r:id="rId1"/></sheets></workbook>');
	$zip->addFromString('xl/_rels/workbook.xml.rels', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/></Relationships>');
	$zip->addFromString('xl/styles.xml', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fonts count="1"><font><sz val="11"/><name val="Calibri"/></font></fonts><fills count="1"><fill><patternFill patternType="none"/></fill></fills><borders count="1"><border/></borders><cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs><cellXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/></cellXfs></styleSheet>');
	$zip->addFromString('xl/sharedStrings.xml', xlsx_shared_strings_xml($strings, $stringCount));
	$zip->addFromString('xl/worksheets/sheet1.xml', xlsx_sheet_xml($rows, $stringMap));
	$zip->close();

	while (ob_get_level()) {
		ob_end_clean();
	}

	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment; filename="' . $file . '"');
	header('Content-Length: ' . filesize($tmp));
	header('Cache-Control: max-age=0');
	header('Pragma: public');
	readfile($tmp);
	unlink($tmp);
	exit;
}

function xls_extra_export_map($conn, $ne, $ids)
{
	$map = array();
	$cleanIds = array();
	foreach ($ids as $id) {
		$id = intval($id);
		if ($id > 0) {
			$cleanIds[$id] = $id;
		}
	}
	if (count($cleanIds) == 0) {
		return $map;
	}
	$ne = mysqli_real_escape_string($conn, $ne);
	$sql = mysqli_query($conn, "select s1,t2,t3,n1,n2,n12,n17,s2,s7,s10,s13 from t1 where t1='" . $ne . "' and (ne='extra' or ne='' or ne is null) and s1 in (" . implode(",", $cleanIds) . ") order by s1,s2,s10,n1 asc");
	while ($row = mysqli_fetch_assoc($sql)) {
		$s1 = intval($row["s1"]);
		if (!isset($map[$s1])) {
			$map[$s1] = array();
		}
		$map[$s1][] = array(
			"t2" => $row["t2"],
			"t3" => $row["t3"],
			"n1" => $row["n1"],
			"n2" => $row["n2"],
			"n12" => $row["n12"],
			"n17" => $row["n17"],
			"s2" => intval($row["s2"]),
			"s7" => intval($row["s7"]),
			"s10" => intval($row["s10"]),
			"s13" => intval($row["s13"])
		);
	}
	foreach ($map as $id => $items) {
		$map[$id] = json_encode($items, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK);
	}
	return $map;
}

$columns = array();
$rw = array();

if (!empty($_POST["exp"]) && is_array($_POST["exp"])) {
	foreach ($_POST["exp"] as $SID) {
		$items = explode(':', $SID, 2);
		if (!empty($items[0]) && !empty($items[1])) {
			$columns[] = $items[0];
			$rw[] = $items[1];
		}
	}
}

$a['index'] = array();
$b['index'] = array();
$replaceDotCol = array();

if ($_GET["ne"] == "kayit") {
	$sorgu = "";
	if ($_POST['c1'] <> 0 && $_POST['c2'] == 0) {
		$sorgu = "and s9='" . $_POST["c1"] . "'";
	} elseif ($_POST['c2'] <> 0 && $_POST['c3'] == 0) {
		$sorgu = "and s10='" . $_POST["c2"] . "'";
	} elseif ($_POST['c3'] <> 0) {
		$sorgu = "and s11='" . $_POST["c3"] . "'";
	}
	if ($_GET['py'] <> "") {
		$sorgu = "" . $sorgu . " and s5<>0 and pb1<>0 and t5<>''";
	}
	$sql = mysqli_query($conn, "select * from " . $_SESSION['lang'] . " where ne='kayit' and (t1 like '%" . $_POST["q"] . "%' or n4 like '%" . $_POST["q"] . "%') $sorgu $luye and s13=1 order by t1 desc");
	$exportRows = array();
	$exportIds = array();
	while ($rs = mysqli_fetch_assoc($sql)) {
		$exportRows[] = $rs;
		$exportIds[] = $rs["id"];
	}
	$extraMap = in_array("maddeli_icerik", $rw) ? xls_extra_export_map($conn, $_GET["ne"], $exportIds) : array();
	foreach ($exportRows as $rs) {
		$jsl = json_decode($rs["n16"], true);
		$jsi = json_decode($rs["n17"], true);
		foreach ($rw as $val) {
			if ($val == "t4") {
				$a['index'][] = i2a($rs[$val], 7);
			} elseif ($val == "n17") {
				$a['index'][] = trim($rs["n17"]) <> "" ? trim($rs["n17"]) : "{}";
			} elseif ($val == "maddeli_icerik") {
				$rowId = intval($rs["id"]);
				$a['index'][] = isset($extraMap[$rowId]) ? $extraMap[$rowId] : "[]";
			} elseif ($val == "pb1" || $val == "pb2") {
				$a['index'][] = number_format($rs[$val], 2, ',', '.');
			} elseif ($val == "s9" || $val == "s10" || $val == "s11" || $val == "t12") {
				$a['index'][] = kategori($rs[$val]);
			} elseif (substr($val, 0, 1) == "i" && $val <> "images" && $val <> "image" && $val <> "id") {
				if ($jsi[$val] <> "") {
					$a['index'][] = str_replace('"', '', json_encode($jsi[$val], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK));
				} else {
					$a['index'][] = "-";
				}
			} elseif ($val == "varyasyon") {
				$var = "";
				$sqlv = mysqli_query($conn, "select t1,pb1,s5 from genel where ne='varyant' and s1=" . $rs["id"] . "");
				while ($rsv = mysqli_fetch_assoc($sqlv)) {
					$var .= "" . $rsv["t1"] . "|" . $rsv["pb1"] . "|" . $rsv["s5"] . ",";
				}
				$a['index'][] = rtrim($var, ",");
			} elseif (substr($val, 0, 5) == "image") {
				$is20 = "";
				$is21 = "";
				$sqlr = mysqli_query($conn, "select t1 from medya where ne='resim' and s1=" . $rs["id"] . " and s2=1");
				while ($rsr = mysqli_fetch_assoc($sqlr)) {
					$filename = "" . $weburl . "/deppo/image/" . $rsr["t1"] . "";
					$imageInfo = @getimagesize($filename);
					if ($imageInfo <> '') {
						$is21 = "" . $weburl . "/deppo/image/" . $rsr["t1"] . "";
					}
				}
				$sqlr = mysqli_query($conn, "select t1 from medya where ne='resim' and s1=" . $rs["id"] . " and s2=0");
				while ($rsr = mysqli_fetch_assoc($sqlr)) {
					$filename = "" . $weburl . "/deppo/image/" . $rsr["t1"] . "";
					$imageInfo = @getimagesize($filename);
					if ($imageInfo <> '') {
						$is20 .= "" . $weburl . "/deppo/image/" . $rsr["t1"] . ",";
					}
				}
				if ($_GET["py"] <> "" && $val == "images") {
					$a['index'][] = "" . $is21 . "," . rtrim($is20, ",") . "";
				} else {
					$a['index'][] = $is21;
				}
			} else {
				if ($rs[$val] <> "") {
					$a['index'][] = trim($rs[$val]);
				} else {
					$a['index'][] = "-";
				}
			}
		}
		$b['index'][] = $a['index'];
		$a['index'] = array();
	}
} elseif ($_GET["ne"] == "uye") {
	$sorgu = "";
	if ($_POST['c1'] <> 0) {
		$sorgu = "where s2='" . $_POST["c1"] . "'";
	}
	$sql = mysqli_query($conn, "select * from t5 $sorgu $luye order by t1 desc");
	$exportRows = array();
	$exportIds = array();
	while ($rs = mysqli_fetch_assoc($sql)) {
		$exportRows[] = $rs;
		$exportIds[] = $rs["id"];
	}
	$extraMap = in_array("maddeli_icerik", $rw) ? xls_extra_export_map($conn, $_GET["ne"], $exportIds) : array();
	foreach ($exportRows as $rs) {
		$jsl = json_decode($rs["n16"], true);
		$jsi = json_decode($rs["n17"], true);
		foreach ($rw as $val) {
			if ($val == "sehir" || $val == "ulke" || $val == "ilce" || $val == "semt") {
				$a['index'][] = loc($jsl[$val]);
			} elseif ($val == "n17") {
				$a['index'][] = trim($rs["n17"]) <> "" ? trim($rs["n17"]) : "{}";
			} elseif ($val == "maddeli_icerik") {
				$rowId = intval($rs["id"]);
				$a['index'][] = isset($extraMap[$rowId]) ? $extraMap[$rowId] : "[]";
			} elseif (substr($val, 0, 1) == "i" && $val <> "ilce") {
				if ($jsi[$val] <> "") {
					$a['index'][] = str_replace('"', '', json_encode($jsi[$val], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK));
				} else {
					$a['index'][] = "-";
				}
			} elseif ($val == "t2" || $val == "t3") {
				$a['index'][] = V3d($rs[$val]);
			} elseif ($val == "puan") {
				$a['index'][] = wcount(getpoint($rs["id"], "", "../../../"));
			} else {
				$a['index'][] = $rs[$val];
			}
		}
		$b['index'][] = $a['index'];
		$a['index'] = array();
	}
} elseif ($_GET["ne"] == "haber") {
	$sql = mysqli_query($conn, "select * from " . $_SESSION['lang'] . " where ne='haber' and (t1 like '%" . $_POST["q"] . "%' or n4 like '%" . $_POST["q"] . "%') $luye order by t1 desc");
	$exportRows = array();
	$exportIds = array();
	while ($rs = mysqli_fetch_assoc($sql)) {
		$exportRows[] = $rs;
		$exportIds[] = $rs["id"];
	}
	$extraMap = in_array("maddeli_icerik", $rw) ? xls_extra_export_map($conn, $_GET["ne"], $exportIds) : array();
	foreach ($exportRows as $rs) {
		$jsi = json_decode($rs["n17"], true);
		foreach ($rw as $val) {
			if ($val == "s2") {
				$a['index'][] = kategori($rs[$val]);
			} elseif ($val == "n17") {
				$a['index'][] = trim($rs["n17"]) <> "" ? trim($rs["n17"]) : "{}";
			} elseif ($val == "maddeli_icerik") {
				$rowId = intval($rs["id"]);
				$a['index'][] = isset($extraMap[$rowId]) ? $extraMap[$rowId] : "[]";
			} elseif (substr($val, 0, 1) == "i" && $val <> "images" && $val <> "image" && $val <> "id") {
				if ($jsi[$val] <> "") {
					$a['index'][] = str_replace('"', '', json_encode($jsi[$val], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK));
				} else {
					$a['index'][] = "-";
				}
			} elseif (substr($val, 0, 5) == "image") {
				$is21 = "";
				$sqlr = mysqli_query($conn, "select t1 from medya where ne='resim' and s1=" . $rs["id"] . " and s2=1");
				while ($rsr = mysqli_fetch_assoc($sqlr)) {
					$filename = "" . $weburl . "/deppo/image/" . $rsr["t1"] . "";
					$imageInfo = @getimagesize($filename);
					if ($imageInfo <> '') {
						$is21 = "" . $weburl . "/deppo/image/" . $rsr["t1"] . "";
					}
				}
				$a['index'][] = $is21;
			} else {
				if ($rs[$val] <> "") {
					$a['index'][] = trim($rs[$val]);
				} else {
					$a['index'][] = "-";
				}
			}
		}
		$b['index'][] = $a['index'];
		$a['index'] = array();
	}
}

exportExcel($_GET["ne"], $columns, $b['index'], $replaceDotCol);
