echajoa/manage/buy/end_list_excel.php

<?php

	include $_SERVER['DOCUMENT_ROOT'].'/common/lib/comm.php';
	include $_SERVER['DOCUMENT_ROOT'].ADMIN_PATH.'common/auth_chk.php';
	include $_SERVER['DOCUMENT_ROOT'].'/vendor/autoload.php';


	$searchtype 	= $_REQUEST['searchtype'];
	$searchword 	= $_REQUEST['searchword'];
	$ds_usertype 	= $_REQUEST['ds_usertype'];
	$dt_start 		= $_REQUEST['dt_start'];
	$dt_end 		= $_REQUEST['dt_end'];
	$ds_status 		= $_REQUEST['ds_status'];

	$add_sql = "where s.ds_delind='N'";
	// $orderby = "order by s.dt_insert desc";
	if(empty($PageSort)) {
		$orderby = "order by s.dt_update desc";
	}else {
		$sort = explode("|", $PageSort);
		$orderby = "order by $sort[0] $sort[1] ";
	}

	$searchtype = $_REQUEST['searchtype'];
	$searchword = $_REQUEST['searchword'];
	$ds_usertype = $_REQUEST['ds_usertype'];
	$ds_status = $_REQUEST['ds_status'];
	$dt_start = $_REQUEST['dt_start'];
	$dt_end = $_REQUEST['dt_end'];

	$ds_type = "A0";	//A0 : 매입
	$add_sql.=  " and s.ds_type = '$ds_type' ";

	if($searchword != ""){
		if($searchtype=="" || $searchtype==null){
			$add_sql.=  " and (s.nm_name like '$searchword%' or a.nm_number like '$searchword%') ";
		}else{
			$add_sql.=  " and $searchtype like '$searchword%'";
		}
	}

	if($dt_start != "" && $dt_end != ""){
		$add_sql.=  " and s.dt_end between '$dt_start' and '$dt_end' ";
	}

	if($ds_usertype != ""){
		$add_sql.=  " and s.ds_usertype = '$ds_usertype'";
	}

	$add_sql.=  " and s.ds_status in ('ZS') ";

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//관계사/지점 Setting
	$admin_ds_company_bp	= $_SESSION['admin_ds_company_bp'];	//관계사
	$admin_ds_branch		= $_SESSION['admin_ds_branch'];		//지점
	$admin_bp_ok			= ($admin_ds_company_bp != "" || $admin_ds_branch != "") ? true : false;	//admin_ds_company_bp 나 admin_ds_branch 값이 하나라도 있으면 관계사로 보고 권한 제한한다. $admin_bp_ok == true 이면 해당 관계사/지점 데이타만 확인 가능하다.

//관계사 일 경우 해당 관계사/지점 데이타만 확인 가능하다.
	// if($admin_bp_ok){
	// 	if($admin_ds_company_bp != "" && $admin_ds_branch != ""){
	// 		$add_sql.=  "	and s.ds_company_bp = '$admin_ds_company_bp' and s.ds_branch = '$admin_ds_branch' ";
	// 	}else{	//admin_ds_company_bp 나 admin_ds_branch 값이 하나라도 없으면 데이타 확인 안되게 한다.
	// 		$add_sql.=  "	and 1 = 2 ";
	// 	}
	// }

	$spreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();
	$sheet = $spreadsheet->getActiveSheet();

	$sql = "SELECT car.*
        , getCode('car_master','ds_type',car.ds_type) as nm_type
        , getCode('car_master','ds_open',car.ds_open) as nm_open
        , getCode('car_master','ds_sales',car.ds_sales) as nm_sales
        , getCode('car_master','ds_transmission',car.ds_transmission) as nm_transmission
        , getCode('member_master','ds_area',car.ds_area_sale) as nm_area
        , getCode('sale_master','ds_status',car.ds_status_sale) as nm_status
        , t.*
        , a.nm_name nm_name_company
FROM (
    SELECT
        a.*
        , b.nm_model
        , c.nm_grade
        , d.nm_brand
        , e.nm_model_sub
        , s.cd_sale
        , s.nm_name nm_name_sale
        , s.nm_recommend nm_recommend_sale
        , s.ds_status ds_status_sale
        , s.ds_status_contract ds_status_contract_sale
        , s.ds_area ds_area_sale
        , s.dt_update dt_update_sale
        , s.dt_insert dt_insert_sale
        , s.dt_success
        , s.dt_end dt_end_sale
        , s.nm_price nm_price_sale
        , s.nm_price_final nm_price_final_sale
        , s.nm_file_trading ds_nm_file_trading
        , s.nm_file_performance ds_nm_file_performance
        , t.nm_price as nm_price_tender
        , t.nm_commission as nm_commission_tender
        , aap.nm_name aap_nm_name
        , (CASE WHEN s.is_staff = 'Y' AND sa.cd_user > '' THEN sa.nm_name ELSE '' END) as sa_name
        , (CASE WHEN s.is_staff = 'Y' AND sa.cd_user > '' THEN sa.nm_hp ELSE '' END) as sa_phone
    		, t.nm_name nm_name_tender
        , t.nm_hp nm_tender_hp
    FROM sale_master s
    INNER JOIN car_master a ON s.cd_car = a.cd_car
    INNER JOIN tender_master t ON t.cd_sale = s.cd_sale AND t.ds_delind = 'N' AND t.ds_status = 'Z0'
    LEFT OUTER JOIN car_model b ON a.cd_model = b.cd_model
    LEFT OUTER JOIN car_grade c ON a.cd_grade = c.cd_grade
    LEFT OUTER JOIN car_brand d ON a.cd_brand = d.cd_brand
    LEFT OUTER JOIN car_model_sub e ON a.cd_model_sub = e.cd_model_sub
    LEFT OUTER JOIN aap_master aap ON aap.cd_dealer = s.cd_aap_dealer
    LEFT OUTER JOIN sa_master sa ON s.cd_user = sa.cd_user
    $add_sql $orderby
) as car
INNER JOIN tender_master t ON car.cd_sale = t.cd_sale AND t.ds_status = 'Z0' AND t.ds_delind = 'N'
INNER JOIN aap_master a ON t.cd_dealer_p = a.cd_dealer";

	$r = mysql_query($sql, $connect);
	// echo $sql;
	// exit;
	$i=1;
	$EXCEL_DATA = array();
	while($col = mysql_fetch_assoc($r)) {
		$nm_file_trading = "X";
		if(!empty($col['ds_nm_file_trading'])){
			$nm_file_trading = "O";
		}
		$nm_file_performance = "X";
		if(!empty($col['ds_nm_file_performance'])){
			$nm_file_performance = "O";
		}

		// $nm_price = number_format($col['nm_price_final_sale'])."만원(".number_format($col['nm_price_tender'])."만원)";
		//2023-11-12 변경 요청
		$nm_price = number_format($col['nm_price_final_sale'])."만원,낙찰금액(".number_format($col['nm_price_tender'])."만원, 수수료 ".number_format($col['nm_commission_tender'])."만원)";

		#리스트
		$DATA  = array();
		$DATA['no'] 			= (string)$i;
		$DATA['nm_name_sale'] 	= $col['nm_name_sale'];
		$DATA['sa_name']    	= $col['sa_name'];
		$DATA['car_info'] 		= $col['nm_brand']." ".$col['nm_model'];
		$DATA['nm_number'] 		= $col['nm_number'];
		$DATA['sa_phone'] 		= $col['sa_phone'];
		$DATA['nm_area'] 		= $col['nm_area'];
		$DATA['nm_price'] 		= $nm_price;
		$DATA['nm_file_trading'] 	= $nm_file_trading;
		$DATA['nm_file_performance'] = $nm_file_performance;
		$DATA['dt_insert_sale'] 	= $col['dt_insert_sale'];
		$DATA['dt_end_sale'] 		= $col['dt_end_sale'];
		$DATA['aap_nm_name'] 		= $col['aap_nm_name'];
		$DATA['nm_name_company'] = $col['nm_name_company'];
		$DATA['nm_name_tender'] = $col['nm_name_tender'];
		$DATA['nm_tender_hp'] = $col['nm_tender_hp'];


		$EXCEL_DATA[] = $DATA;
		unset($DATA);

		$i++;
	}
	mysql_close($connect);

	$cells = array(
		'A' => array(15, 'no', '번호'),
		'B' => array(20, 'nm_name_sale',  '판매자'),
		'C' => array(20, 'sa_name',  'SA명'),
		'D' => array(20, 'sa_phone', 'SA연락처'),
		'E' => array(20, 'aap_nm_name', '희망AAP'),
		'F' => array(40, 'car_info', '차종'),
		'G' => array(20, 'nm_number', '차량번호'),
		'H' => array(20, 'nm_area', '판매지역'),
		'I' => array(20, 'nm_name_company', '낙찰업체'),
		'J' => array(20, 'nm_name_tender', '낙찰담당자'),
		'K' => array(20, 'nm_tender_hp', '연락처'),
		'L' => array(20, 'nm_price', '판매완료 금액(낙찰금액)'),
		'M' => array(20, 'nm_file_trading', '등록증'),
		'N' => array(20, 'nm_file_performance', '성능지'),
		'O' => array(20, 'dt_insert_sale', '등록일'),
		'P' => array(20, 'dt_end_sale', '판매완료일'),
	);

	foreach ($cells as $key => $val) {
		$cellName = $key.'1';
		$sheet->getColumnDimension($key)->setWidth($val[0]);
		$sheet->getRowDimension('1')->setRowHeight(25);
		$sheet->setCellValue($cellName, $val[2]);
		$sheet->getStyle($cellName)->getFont()->setBold(true);
		$sheet->getStyle($cellName)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
		$sheet->getStyle($cellName)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
	}
	for ($i = 2; $row = array_shift($EXCEL_DATA); $i++) {
		foreach ($cells as $key => $val) {
			$sheet->setCellValue($key.$i, $row[$val[1]]);
		}
	}

	$filename = '관리자_판매완료_엑셀_'.date('Y-m-d');
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment; filename="'.$filename.'.xlsx"');
	$writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
	$writer->save('php://output');

	exit;

?>