<?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";          //2200513 등록일 날짜 기준으로 리스트 정렬하기
//$orderby = "order by s.dt_update desc";		//20191207 디비에 업데이트 날짜 기준으로 리스트 정렬하기
if(empty($PageSort)) {
	$orderby = "order by s.dt_insert desc";
}else {
	$sort = explode("|", $PageSort);
	$orderby = "order by $sort[0] $sort[1] ";
}

$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($ds_usertype != ""){
	$add_sql.= "	and s.ds_usertype = '$ds_usertype'";
}

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

$add_sql.= "	and s.ds_status in ('E0','F0')";

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//관계사/지점 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();


//-- , getRemainTimeByPhase(".LIMIT_TIME_TENDER.", ".LIMIT_TIME_SELL.", dt_approve_sale, now()) as time_limit_sell
//  , getRemainTimeByPhase(ds_auction_time, ds_auction_time, dt_approve_sale, now()) as time_limit_sell


$sql = "SELECT *
				, getCode('car_master','ds_type',ds_type) as nm_type
				, getCode('car_master','ds_open',ds_open) as nm_open
				, getCode('car_master','ds_sales',ds_sales) as nm_sales
				, getCode('car_master','ds_transmission',ds_transmission) as nm_transmission
				, getCode('member_master','ds_area',ds_area_sale) as nm_area
				, getCode('sale_master','ds_status',ds_status_sale) as nm_status

				FROM (
						SELECT
							a.*
							, b.nm_model
							, d.nm_brand
							, s.cd_sale
							, s.nm_name nm_name_sale
							, s.nm_hp nm_hp_sale
							, s.nm_recommend nm_recommend_sale
							, s.cd_aap_dealer cd_aap_dealer_sale
							, s.ds_status ds_status_sale
							, s.ds_status_contract ds_status_contract_sale
							, s.ds_area ds_area_sale
							, s.dt_approve dt_approve_sale
							, s.dt_update dt_update_sale
							, s.dt_insert dt_insert_sale
							, s.cd_user as cd_user
							, 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						
							FROM sale_master s
								inner join car_master a on (s.cd_car = a.cd_car)
								left outer join car_model b on (a.cd_model = b.cd_model)
								left outer join car_brand d on (a.cd_brand = d.cd_brand)
								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
	";
// echo $sql;

// exit;
$r = mysql_query($sql, $connect);

$i=1;
$EXCEL_DATA = array();
while($col = mysql_fetch_assoc($r)) {
	#리스트
	$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_mileage'] 	= $col['nm_mileage']."km";
	$DATA['nm_color'] 		= $col['nm_color'];
	$DATA['nm_area'] 		= $col['nm_area'];
	$DATA['cd_user']			= $col['cd_user'];
	$DATA['time_limit_sell'] 	= $col['time_limit_sell'];
	$DATA['dt_insert'] 		= $col['dt_insert_sale'];
	$DATA['dt_update'] 		= $col['dt_update_sale'];
	$DATA['aap_nm_name'] 		= $col['aap_nm_name'];


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

	$i++;
}

// 데이터 순서를 역순으로 뒤집기
$EXCEL_DATA = array_reverse($EXCEL_DATA);

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_mileage', '주행거리'),
	'I' => array(20, 'nm_color', '색상'),
	'J' => array(20, 'nm_area', '판매지역'),
	'K' => array(20, 'dt_update', '판매시간'),
	'L' => array(20, 'dt_insert', '등록일')
);

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;
?>