echajoa/m/mypage/dealer_tender_list.php.20200707

<?php
	include $_SERVER['DOCUMENT_ROOT'].'/common/lib/comm.php';
	include $_SERVER['DOCUMENT_ROOT'].'/common/dealer_chk.php';

	$s_cd_dealer = $_SESSION['s_cd_dealer'];
	$s_cd_dealer_p = $_SESSION['s_cd_dealer_p'];
	$s_ds_type = $_SESSION['s_ds_type'];

	$s_ds_usertype = $_SESSION['s_ds_usertype'];
	$s_ds_company_bp1 = $_SESSION['s_ds_company_bp1'];
	$s_ds_branch1 = $_SESSION['s_ds_branch1'];
	$s_ds_company_bp2 = $_SESSION['s_ds_company_bp2'];
	$s_ds_branch2 = $_SESSION['s_ds_branch2'];
	$s_ds_company_bp3 = $_SESSION['s_ds_company_bp3'];
	$s_ds_branch3 = $_SESSION['s_ds_branch3'];

	$PageNo = $_REQUEST['pageno'];
	$PageSize = $_REQUEST['PageSize'];
	if (empty($PageNo)) {$PageNo=1;}else{$PageNo=$PageNo;}
	if (empty($PageSize)) {
		$PageSize=8; //목록개수설정
	}else{$PageSize=$PageSize;}

	$pagestartpoint=($PageNo-1)*$PageSize;
	$sql_from = "";
	$sql = "where s.ds_delind='N'";
	$orderby = "order by s.dt_insert desc";

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

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

	if($ds_status == "E0"){	//입찰하지 않은 판매건
		$sql = $sql. "	and s.dt_approve >=  DATE_ADD(now(), INTERVAL '-".LIMIT_TIME_TENDER."' HOUR) ";

		if($s_ds_usertype == "B0"){	//kcar 딜러
		$sql = $sql. "	and (
								(
								s.ds_company_bp = '$s_ds_company_bp1' and ds_branch = '$s_ds_branch1'
								)
								or (
								s.ds_company_bp = '$s_ds_company_bp2' and ds_branch = '$s_ds_branch2'
								)
								or (
								s.ds_company_bp = '$s_ds_company_bp3' and ds_branch = '$s_ds_branch3'
								)
			) ";
		}else{
		$sql = $sql. "	and s.ds_area in (
							select
							   SUBSTRING_INDEX (SUBSTRING_INDEX(nm_area,',',numbers.n),',',-1)
							from
							   (select  1 n union  all  select 2
								union  all  select  3  union  all select 4
								union  all  select  5  union  all  select  6
								union  all  select  7  union  all  select  8
								union  all  select  9 union  all  select  10
								union  all  select  11 union  all  select  12
								union  all  select  13 union  all  select  14
								union  all  select  15 union  all  select  16
								union  all  select  17 union  all  select  18
								union  all  select  19 union  all  select  20
								) numbers INNER  JOIN dealer_master
								on CHAR_LENGTH ( nm_area ) - CHAR_LENGTH ( REPLACE ( nm_area ,  ',' ,  '' ))>= numbers . n-1
							where cd_dealer = $s_cd_dealer_p
			) ";
		}

	}

	if($s_ds_type == "D0"){	//딜러
		$sql_ds_type = "	and cd_dealer_p = '$s_cd_dealer' ";
	}else{	//딜러-관리자
		$sql_ds_type = "	and cd_dealer = '$s_cd_dealer' ";
	}

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

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

	if($ds_status == "E0"){	//딜러 업체 소속 입찰 건이 2건 보다 작을 시만 입찰 요청 건에 보이게 한다.	//입찰하지 않은 판매건
		$sql = $sql. "	and s.ds_status in ('E0','F0')";

		//딜러 업체 소속 입찰 건이 2건 보다 작을 시만 입찰 요청 건에 보이게 한다.
		$sql_from = $sql_from. "	left outer join ( ";
		$sql_from = $sql_from. "	select cd_sale from tender_master where ds_delind = 'N' and cd_dealer_p = '$s_cd_dealer_p' group by cd_sale having count(*) > 1) f ";
		$sql_from = $sql_from. "	on (s.cd_sale = f.cd_sale)  ";
		$sql = $sql. "	and f.cd_sale is null ";	//딜러 업체 소속 입찰 건이 2건 보다 작을 시만 입찰 요청 건에 보이게 한다.

		if($s_ds_type == "D1"){	//딜러-관리자
		//본인 입찰 건 제외한 입찰 건
		$sql_from = $sql_from. "	left outer join ( ";
		$sql_from = $sql_from. "	select cd_sale from tender_master where ds_delind = 'N' and cd_dealer = '$s_cd_dealer' group by cd_sale) g ";
		$sql_from = $sql_from. "	on (s.cd_sale = g.cd_sale) ";

		$sql = $sql. "	and g.cd_sale is null ";	//본인 입찰 건 제외한 입찰 건
		}

	}else{
		if($ds_status == "F0"){	//입찰 중인 판매건
			$sql = $sql. "	and s.ds_status in ('E0','F0')";
//			$sql = $sql. "	and s.cd_sale in (select cd_sale from tender_master where ds_delind = 'N' ".$sql_ds_type." group by cd_sale)";	//20191001이전 지연 쿼리
			$sql = $sql. "	and exists(select cd_sale from tender_master where cd_sale = s.cd_sale and ds_delind = 'N' ".$sql_ds_type." )";	//20191001 cd_sale_cd_user_cd_dealer index 설정 후 쿼리 변경
		}else if($ds_status == "Z0"){	//낙찰된 판매건
			$sql = $sql. "	and s.ds_status in ('Z0')";

			$sql_from = $sql_from. "	inner join ( ";
			$sql_from = $sql_from. "	select cd_sale from tender_master where ds_delind = 'N' and ds_status in ('Z0') ".$sql_ds_type." group by cd_sale) h ";
			$sql_from = $sql_from. "	on (s.cd_sale = h.cd_sale)  ";
//			$sql = $sql. "	and h.cd_sale is not null ";

		}else if($ds_status == "ZS"){	//구매 확정된 판매건
//			$sql = $sql. "	and s.ds_status in ('Z0')";
//			$sql = $sql. "	and s.ds_status_contract in ('Z0')";
			$sql = $sql. "	and s.ds_status in ('ZS')";

			$sql_from = $sql_from. "	inner join ( ";
			$sql_from = $sql_from. "	select cd_sale from tender_master where ds_delind = 'N' and ds_status in ('Z0') ".$sql_ds_type." group by cd_sale) i ";
			$sql_from = $sql_from. "	on (s.cd_sale = i.cd_sale)  ";
//			$sql = $sql. "	and i.cd_sale is not null ";

		}else if($ds_status == "ZE"){	//유찰
			/*
			관리자의 해약 리스트는 낙찰 상태의 거래 실패의 경우 리스트를 가져 오며,
			사이트에서 유찰상태에 대한 정의가 딱히 정해 진게 없어서 아래의 조건에 해당되는 데이터를 가져온다
			2020-02-02

			관리자 해약 리스트 : and s.ds_status in ('Z0') and s.ds_status_contract in ('C0')


			1. 낙찰상태로 변경됐으나 본인이 안된 경우
		        - sale_master.ds_status = 'Z0' and tender_master.ds_status = 'A0'
		    2. 낙찰상태로 변경 됐고 본인이 된 경우
		        1) 거래 실패 처리 할 경우
		            - sale_master.ds_status = 'Z0' and ds_status_contract = 'C0'
		        2) 견적 실수로 처리 할 경우
		            - sale_master.ds_status = 'ZE'
		    3. 입찰 시간 초과로 경매 종료 됐을 경우
		        - sale_master.ds_status = 'E0' and dt_approve + 낙찰시간 < 현재시간
			*/

			$sql_from.= " inner join (
							-- 낙찰상태로 변경됐으나 본인이 안된 경우
							select s.cd_sale from sale_master s
							inner join (
								select cd_sale
									from tender_master
										where ds_status = 'A0'
											$sql_ds_type
									group by cd_sale) i  on (s.cd_sale = i.cd_sale)
							where s.ds_status = 'Z0'

							union
							-- 2. 낙찰상태로 변경 됐고 본인이 된 경우
							--     1) 거래 실패 처리 할 경우
							--     2) 견적 실수로 처리 할 경우
							-- 3. 입찰 시간 초과로 경매 종료 됐을 경우
							select s.cd_sale from sale_master s
							inner join (
								select cd_sale
									from tender_master
										where ds_status = 'Z0'
											$sql_ds_type
									group by cd_sale) i  on (s.cd_sale = i.cd_sale)
							where ((s.ds_status = 'Z0' and s.ds_status_contract = 'C0')
									or s.ds_status = 'ZE'
									or (s.ds_status = 'E0' and getRemainTimeByPhase('".LIMIT_TIME_TENDER."', '".LIMIT_TIME_SELL."', s.dt_approve, now()) = '0분'))
						) sum_a on sum_a.cd_sale = s.cd_sale";

		}else{
			$sql = $sql. "	and 1!=1 ";
		}
	}

	if($ds_status == "E0"){
		$nm_title = "입찰대기 리스트";
	}else if($ds_status == "F0"){
		$nm_title = "입찰중 리스트";
	}else if($ds_status == "Z0"){
		$nm_title = "낙찰 리스트";
	}else if($ds_status == "ZE"){
		$nm_title = "유찰 리스트";
	}else if($ds_status == "ZS"){
		$nm_title = "구매확정 리스트";
	}

	$totalrows = @mysql_result(mysql_query("select count(*) from sale_master s inner join car_master a on (	s.cd_car = a.cd_car ) $sql_from $sql"),0,0);

?>

<!DOCTYPE html>
<HTML lang="ko">
<head>
<meta charset="utf-8">
<title>UPRO</title>
<meta name="viewport" content="user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0, width=device-width">
<meta name="format-detection" content="telephone=no">
<link rel="stylesheet" type="text/css" href="/m/css/common.css">
<link rel="stylesheet" type="text/css" href="/m/css/sub.css">
<script type="text/javascript" charset="utf-8" src="/m/js/jquery-1.11.2.min.js"></script>
<script type="text/javascript" charset="utf-8" src="/m/js/ui.js"></script>
</head>

<body>
<!-- s : header -->
<? include("../inc/inc_header.php"); ?>
<!-- // e : header -->

<!-- s : container -->
<section id="container">
	<section id="visual" class="visual_mypage">
		<h2>마이페이지</h2>
		<p>쉽고 간편한 내차팔기, 견적산출,<br>최종 매각까지 어떤 수수료도 요구하지 않습니다.</p>
	</section>

	<section id="content">
    <? include("./inc_menu_dealer.php") ?>


	<!--	<p class="sort">
			<select style="width:100%;">

					<option value="">최근</option>
					<option value="">종료임박순</option>
					<option value="">응찰적은순</option>

			</select>
		</p>-->
		<ul class="car_list">
  <?
if($totalrows>0) {

$r = mysql_query("SELECT
						a.*
						, b.nm_model
						, c.nm_grade
						, d.nm_brand
						, e.nm_model_sub
						, getCode('car_master','ds_type',a.ds_type) as nm_type
						, getCode('car_master','ds_open',a.ds_open) as nm_open
						, getCode('car_master','ds_sales',a.ds_sales) as nm_sales
						, getCode('car_master','ds_transmission',a.ds_transmission) as nm_transmission
						, getCode('member_master','ds_area',s.ds_area) as nm_area
						, getCode('sale_master','ds_status',s.ds_status) as nm_status
						, s.cd_sale
						, s.nm_name nm_name_sale
						, s.ds_status ds_status_sale
						, case when s.ds_status in ('B0','C0') then '미승인' when s.ds_status in ('D0') then '반려' when s.ds_status in ('E0','F0') then case when 'E0' = '$ds_status' then '입찰대기' else '입찰중' end when s.ds_status in ('G0') then '입찰마감' when s.ds_status in ('Z0') then '낙찰' when s.ds_status in ('ZS') then '구매확정' else '' end as nm_status_sale
						, case when a.ds_accident in ('A0') then '유'  when a.ds_accident in ('B0') then '무' end as nm_act_yn_name
						, getRemainTime(".LIMIT_TIME_TENDER.", s.dt_approve, now()) as time_limit_tender
						, getRemainTime(".LIMIT_TIME_SELL.", s.dt_approve, now()) as time_limit_sell
						, ifnull((select min(nm_price) from tender_master where ds_delind = 'N' and cd_sale = s.cd_sale ".$sql_ds_type."), '') nm_price_tender
						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_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)
							$sql_from
							$sql $orderby LIMIT $pagestartpoint,$PageSize");

$i=1;
while($col = mysql_fetch_array($r)) {

	$tmpNum =$totalrows-($PageSize*($PageNo-1));
	$no = ($tmpNum - $i)+1;

?>
<?
	if($col[ds_status_sale] == "ZS"){
?>
			<li onclick="location.href='./dealer_tender_view3.php?list_url=<?=$list_url?>&cd_sale=<?=$col[cd_sale]?>&ds_status_sale=<?=$col[ds_status_sale]?>&ds_status=<?=$ds_status?>&ListPageNo=<?=$PageNo?>';" style="cursor:pointer;">
<?
	}else if($col[ds_status_sale] == "Z0" && $ds_status != "ZE"){
?>
			<li onclick="location.href='./dealer_tender_view2.php?list_url=<?=$list_url?>&cd_sale=<?=$col[cd_sale]?>&ds_status_sale=<?=$col[ds_status_sale]?>&ds_status=<?=$ds_status?>&ListPageNo=<?=$PageNo?>';" style="cursor:pointer;">
<?
	}else{
?>
			<li onclick="location.href='./dealer_tender_view.php?list_url=<?=$list_url?>&cd_sale=<?=$col[cd_sale]?>&ds_status_sale=<?=$col[ds_status_sale]?>&ds_status=<?=$ds_status?>&ListPageNo=<?=$PageNo?>';" style="cursor:pointer;">
<?
	}
?>

				<div>
					<span class="thumb"><img src="<?=UPLOAD_PATH_CAR?>nm_file_main/ratio/<?=$col[nm_file_front]?>" alt=""></span>
					<dl>
						<dt><?=$col[nm_model]." ".$col[nm_model_sub]." ".$col[nm_grade]?></dt>
						<dd>주행거리 : <?=$col[nm_mileage]?>km</dd>
						<dd>최초등록일 : <?=$col[nm_regdate_mm]?> / <?=$col[nm_regdate_yyyy]?></dd>
						<dd>사고유무 : <?=$col[nm_act_yn_name]?></dd>
						<dd>판매지역 : <?=$col[nm_area]?></dd>
                        <?
	if($ds_status != "E0"){	//입찰하지 않은 판매건
?>
					<dd>입찰금액 : <?=$col[nm_price_tender]?>만원  </dd>
<?
	}
?>

<? if($ds_status == "E0" || $ds_status == "F0"){ ?>
                    <dd>마감시간 : <?=$col[time_limit_tender]?></dd>
				<? } ?>
				</dl>
				<div class="flag">
					<p>
				<? if($col[time_limit_sell]=="0분" && ($col[ds_status_sale] == "E0" || $col[ds_status_sale] == "F0")){ ?>
				거래마감
				<? }else{ ?>
					<? if($ds_status == "ZE") {?>
						유찰
					<? } else {?>
						<?=$col[nm_status_sale]?>
					<? } ?>
				<? } ?>

					</p>
				</div>
			</li>
            <?
	$i++;
}
}else{

            ?>
			<li class="info_no">
				등록된 정보가 없습니다.
			</li>
<?
}
?>

<!--			<li>
				<div>
					<span class="thumb"></span>
					<dl>
						<dt>GLA-Class 200d 4M</dt>
						<dd>주행거리 : 28,980km</dd>
						<dd>최초등록일 : 11 / 2017</dd>
						<dd>사고유무 : 무</dd>
						<dd>판매지역 : 서울</dd>
						<dd>마감시한 : 9시간 30분</dd>
					</dl>
				</div>
			</li>-->

		</ul>

		<div class="paging">

        	<?
					$url = "./dealer_tender_list.php?searchtype=$searchtype&searchword=$searchword&ds_status=$ds_status&ds_usertype=$ds_usertype";
					ListPg1($url ,$PageNo,$PageSize,10,$totalrows,$leftboard[1],$rootpath);
			?>


		<!--	<span>
				<a href="#" class="first">맨처음</a>
				<a href="#" class="prev">이전</a>
			</span>
			<strong>1</strong>
			<a href="#">2</a>
			<a href="#">3</a>
			<a href="#">4</a>
			<a href="#">5</a>
			<span>
				<a href="#" class="next">다음</a>
				<a href="#" class="last">맨마지막</a>
			</span>-->
		</div>
	</section>
</section>
<!-- e : container -->

<!-- s : footer -->
<? include("../inc/inc_footer.php"); ?>
<!-- // e : footer -->

</body>
</html>

<?
mysql_close($connect);
?>