echajoa/mypage/dealer_tender_list.php

<?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 딜러
			/*
			20210130 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'
								)
			) ";
			*/
			$sql = $sql. "	and ds_branch > ''
							and s.ds_company_bp in ('$s_ds_company_bp1', '$s_ds_company_bp2', '$s_ds_company_bp3' )
			";
		}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 설정 후 쿼리 변경
			$sql = $sql. " and getRemainTimeByPhase('".LIMIT_TIME_TENDER."', '".LIMIT_TIME_SELL."', s.dt_approve, now()) != '0분' ";//20200707 입찰마감 시간 지난 건 안보이게 처리
		}else if($ds_status == "Z0"){	//낙찰된 판매건
			//검차 결과가 없거나 거래완료인 매물만 나오게 수정 2021-07-15
			// $sql = $sql. "	and s.ds_status in ('Z0')";
			$sql = $sql. "	and s.ds_status in ('Z0')  and (s.ds_status_contract = '' or s.ds_status_contract = '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_column = ", sum_a.fail_type , s.nm_price as nm_price_sale ";

			$sql_from.= " inner join (
							-- 낙찰상태로 변경됐으나 본인이 안된 경우
							-- 단 유프로는 오류로 인해 입찰이 한번이 아닌 여러번이 될 수도 있음
							select s.cd_sale, '1' as fail_type from sale_master s
							inner join (
								select cd_sale
									from tender_master
										where ds_status = 'A0'
											$sql_ds_type
											and cd_sale not in(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 in ('Z0', 'ZS')

							union
							-- 2. 낙찰상태로 변경 됐고 본인이 된 경우
							--     1) 거래 실패 처리 할 경우
							--     2) 견적 실수로 처리 할 경우

							select s.cd_sale, '2' as fail_type 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'
								)

							union
							-- 3. 입찰 시간 초과와 입찰중인 상태일 경우
							select s.cd_sale, '3' as fail_type from sale_master s
							inner join (
								select cd_sale
									from tender_master
										where 1
											$sql_ds_type
									group by cd_sale) i  on (s.cd_sale = i.cd_sale)
							where (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 = "구매확정 리스트";
	}

	if($_SERVER['REMOTE_ADDR'] == '220.86.24.199') {
		// echo "select count(*) from sale_master s inner join car_master a on (	s.cd_car = a.cd_car ) $sql_from $sql";
	}

	$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);

?>
<? include("../include/inc_top.php") ?>
<? include("../include/inc_header.php") ?>

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

	<section id="content">
		<ul class="location">
			<li><a href="/" class="home">홈</a></li>
			<li><span class="depth1">마이페이지</span>
				<ul class="sub_depth">
					<li><a href="#">마이페이지</a></li>
					<li><a href="#">입찰정보</a></li>
					<li><a href="#">차량정보</a></li>
					<li><a href="#">문의내역</a></li>
				</ul>
			</li>
			<li><span class="depth2">딜러</span></li>
		</ul>

<? include("./inc_menu_dealer.php") ?>

		<h3><?=$nm_title?></h3>
		<div class="select_sort">
			<span class="select">
				<select>
					<option value="">최근</option>
					<option value="">종료임박순</option>
					<option value="">응찰적은순</option>
				</select>
			</span>
		</div>
		<ul class="car_list ty2">
<?
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
						, t.cd_tender cd_tender
						, t.ds_lock_status ds_lock_status
						$sql_column
						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)
							left outer join tender_master t on (s.cd_sale = t.cd_sale and t.ds_status = 'Z0' and t.ds_delind = 'N')
							$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" && $ds_status != "ZE"){
?>
			<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"){
		$lock_border = "";
		if($col[ds_lock_status] == 'A0') {
			$lock_border = "border: 3px solid #ff0000;";
		}
?>
			<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;<?=$lock_border?>">
<?
	}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;">
<?
	}
?>
				<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>
				<? } ?>
				<?
					if($ds_status == "ZE") {
						if($col[fail_type] == 1) {
				?>
								<dd>낙찰금액 : <?=$col[nm_price_sale]?>만원  </dd>
				<?
						}
					}
				?>
				</dl>
				<div class="flag">
					<?
						if($ds_status == "ZE") {
							if($col[fail_type] == 1) {
					?>
						낙찰
						<? }else { ?>
						유찰
					<?
							}
						} else {
					?>
						<? if($col[time_limit_sell]=="0분" && ($col[ds_status_sale] == "E0" || $col[ds_status_sale] == "F0")){ ?>
							거래마감
						<? }else{ ?>
							<?=$col[nm_status_sale]?>
						<? } ?>
					<? } ?>
				</div>
			</li>
<?
	$i++;
}
}else{
?>
			<li class="info_no">
				등록된 정보가 없습니다.
			</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);
			?>
		</div>
	</section>
</section>

<font color="white"><?= "" ?></font>

<? include("../include/inc_footer.php") ?>
<? include("../include/inc_bottom.php") ?>
<?
mysql_close($connect);
?>