echajoa/manage/buy/underway_list_excel.php20240726

<?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] ";
  }

  $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_success between '$dt_start' and '$dt_end' ";
  }

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

  $add_sql.= " and s.ds_status in ('Z0') and (s.ds_status_contract is null or s.ds_status_contract = '' or s.ds_status_contract = 'Z0') and s.ds_status_contract_step1 = 'Y' ";

  ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  //관계사/지점 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 *
    , 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
                , 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 dt_success_sale
                , s.nm_price nm_price_sale
                , s.nm_price_final nm_price_final_sale
                , s.ds_status_contract_step1 ds_status_contract_step1_sale
                , s.ds_status_contract_step2 ds_status_contract_step2_sale
                , s.ds_status_contract_step3 ds_status_contract_step3_sale
                , s.ds_status_contract_step4 ds_status_contract_step4_sale
                , f.ds_complete_depth ds_complete_depth
                , f.cd_tender cd_tender
                , f.ds_underway_depth ds_underway_depth
        ";
  // echo $sql;
  // exit;
  $r = mysql_query($sql, $connect);

  $i=1;
  $EXCEL_DATA = array();
  while($col = mysql_fetch_assoc($r)) {

    //단계 추가
    $step = "";
    if($col['ds_status_contract_step4_sale'] == "Y") {
      $step = "거래마무리";
    }else if($col['ds_status_contract_step3_sale'] == "Y") {
      $step = "계약상황";
    }else if($col['ds_status_contract_step2_sale'] == "Y") {
      $step = "일정잡기";
    }else if($col['ds_status_contract_step1_sale'] == "Y") {
      $step = "차량정보확인";
    }

    #리스트
    $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['step'] 			= $step;
    $DATA['dt_insert_sale'] = $col['dt_insert_sale'];
    $DATA['dt_success_sale'] = $col['dt_success_sale'];
    $DATA['aap_nm_name'] 		= $col['aap_nm_name'];


    $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, 'step', '단계'),
    'J' => array(20, 'dt_insert_sale', '등록일'),
    'K' => array(20, 'dt_success_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;

?>