<?php
require_once($_SERVER['DOCUMENT_ROOT'] . "/session.php");
require_once($_SERVER['DOCUMENT_ROOT'] . "/lib/mydb.php");
$pdo = db_connect();

$month = $_POST['month'];
$year = $_POST['year'];
$selectedFilter = $_POST['selectedFilter'];
$search = $_POST['search'] ?? null;
$radioarray = $_POST['radioarray'] ?? [];

$todo_data = array();
$leave_data = array();
$holiday_data = array();
$shutter_data = array();
$registration_data = array();

function applySearchCondition($query, $columns, $search) {
    if ($search) {
        $searchConditions = [];
        foreach ($columns as $column) {
            $placeholder = "%" . $search . "%";
            $searchConditions[] = "$column LIKE '$placeholder'";
        }
        $query .= " AND (" . implode(" OR ", $searchConditions) . ")";
    }
    return $query;
}

// 라디오버튼 기타
try {
    // todos 테이블
    if ($selectedFilter == 'filter_etc' || $selectedFilter == 'filter_all') {
        $query = "SELECT num, orderdate, towhom, reply, deadline, work_status, title, first_writer, update_log, searchtag 
                  FROM " . $DB . ".todos 
                  WHERE is_deleted IS NULL";
        
        // 검색어가 있는 경우 날짜 조건을 제외
        if (!$search) {
            $query .= " AND MONTH(orderdate) = $month AND YEAR(orderdate) = $year";
        }
        
        $query = applySearchCondition($query, ['towhom', 'reply', 'title'], $search);
        $query .= " ORDER BY num DESC";

        $stmh = $pdo->query($query);
        while ($row = $stmh->fetch(PDO::FETCH_ASSOC)) {
            array_push($todo_data, $row);
        }
    }

    // eworks 테이블
    if ($selectedFilter == 'filter_al' || $selectedFilter == 'filter_all') {
        $query = "SELECT author, al_askdatefrom, al_askdateto, al_item, al_content  
                  FROM " . $DB . ".eworks 
                  WHERE is_deleted IS NULL and  al_company='경동' ";  // 경동기업만 보기
        
        // 검색어가 있는 경우 날짜 조건을 제외
        if (!$search) {
            $query .= " AND ((MONTH(al_askdatefrom) = $month AND YEAR(al_askdatefrom) = $year) 
                      OR (MONTH(al_askdateto) = $month AND YEAR(al_askdateto) = $year))";
        }

        $query = applySearchCondition($query, ['author', 'al_item', 'al_content'], $search);
        $query .= " ORDER BY num DESC";

        $stmh = $pdo->query($query);
        while ($row = $stmh->fetch(PDO::FETCH_ASSOC)) {
            array_push($leave_data, $row);
        }
    }

    // holiday 데이터 가져오기
    $stmh = $pdo->query("SELECT num, startdate, enddate, comment 
                         FROM " . $DB . ".holiday 
                         WHERE is_deleted IS NULL 
                         AND ((MONTH(startdate) = $month AND YEAR(startdate) = $year) 
                         OR (MONTH(enddate) = $month AND YEAR(enddate) = $year))");

    while($row = $stmh->fetch(PDO::FETCH_ASSOC)) {
        array_push($holiday_data, $row);
    }
	
	// 통합 데이터 생성
	$integratedData = [];
	// 중복 체크용 배열 (num 값만 저장)
	$existingNums = [];

	// 출고일기준 테이블
	if ($selectedFilter == 'filter_shutter' || $selectedFilter == 'filter_all') {
		$query = "SELECT num, outworkplace, outputplace, outdate, indate, receiver, secondord    
				  FROM {$DB}.output  
				  WHERE is_deleted = '0' and (devMode <> '1' OR devMode IS NULL) ";

		if (!$search) {
			$query .= " AND ((MONTH(outdate) = $month AND YEAR(outdate) = $year) 
					  OR (MONTH(outdate) = $month AND YEAR(outdate) = $year))";
		}

		$query = applySearchCondition($query, ['outworkplace', 'outputplace', 'receiver', 'secondord'], $search);
		$query .= " ORDER BY num DESC";

		$stmh = $pdo->query($query);
		while ($row = $stmh->fetch(PDO::FETCH_ASSOC)) {
			array_push($shutter_data, $row);
		}

		// shutter_data 데이터 통합 (중복 제거)
		foreach ($shutter_data as $row) {
			if (!in_array($row['num'], $existingNums)) { // 중복 체크
				$integratedData[] = [
					"table" => "output", // 테이블 이름
					"num" => $row['num'],
					"outworkplace" => $row['outworkplace'],
					"outputplace" => $row['outputplace'] ?? '',  
					"receiver" => $row['receiver'] ?? '',
					"secondord" => $row['secondord'] ?? '',
					"outdate" => $row['outdate'] ?? '',
					"indate" => $row['indate'] ?? ''
				];
				$existingNums[] = $row['num']; // 추가된 num 저장
			}
		}
	}

	// 접수일기준 테이블
	if ($selectedFilter == 'filter_registration' || $selectedFilter == 'filter_all') {
		$query = "SELECT num, outworkplace, outputplace, indate, outdate, receiver, secondord    
				  FROM {$DB}.output  
				  WHERE is_deleted = '0'  and (devMode <> '1' OR devMode IS NULL)  ";

		if (!$search) {
			$query .= " AND ((MONTH(indate) = $month AND YEAR(indate) = $year) 
					  OR (MONTH(indate) = $month AND YEAR(indate) = $year))";
		}

		$query = applySearchCondition($query, ['outworkplace', 'outputplace', 'receiver', 'secondord'], $search);
		$query .= " ORDER BY num DESC";

		$stmh = $pdo->query($query);
		while ($row = $stmh->fetch(PDO::FETCH_ASSOC)) {
			array_push($registration_data, $row);
		}

		// registration_data 데이터 통합 (중복 제거)
		foreach ($registration_data as $row) {
			if (!in_array($row['num'], $existingNums)) { // 중복 체크
				$integratedData[] = [
					"table" => "output", // 테이블 이름
					"num" => $row['num'],
					"outworkplace" => $row['outworkplace'],
					"outputplace" => $row['outputplace'] ?? '',  
					"receiver" => $row['receiver'] ?? '',
					"secondord" => $row['secondord'] ?? '',
					"outdate" => $row['outdate'] ?? '',
					"indate" => $row['indate'] ?? ''
				];
				$existingNums[] = $row['num']; // 추가된 num 저장
			}
		}
	}

	// 날짜 역순 정렬
	usort($integratedData, function ($a, $b) {
		$dateA = $a['outdate'] ?: $a['indate']; // outdate 없으면 indate 사용
		$dateB = $b['outdate'] ?: $b['indate'];
		return strcmp($dateB, $dateA); // 최신 날짜 기준 역순 정렬
	});


    // 응답 데이터 구성
    $response = array(
        "todo_data" => $todo_data,
        "leave_data" => $leave_data,
        "holiday_data" => $holiday_data,  
        "shutter_data" => $shutter_data,  
        "registration_data" => $registration_data,  
        "search" => $search,
        "integratedData" => $integratedData,		
    );

    echo(json_encode($response, JSON_UNESCAPED_UNICODE));

} catch (PDOException $Exception) {
    print "오류: ".$Exception->getMessage();
}
?>