laravel长sql查询

                  $sql = <<<sql
-- p1 下单时间 订单状态 异常原因  城市 订单号 承租人 取车时间 还车时间 车品牌 车型 车牌号
-- p5 租金:天数 日租金 提前取车/延迟还车 退款/提前还车(退还客户金额) 金额合计
-- p4 服务费: 尊享服务费 车损无忧 (不计免赔) 服务手续费	夜间服务费	取/送车服务费	异地取/还车	其他	金额合计
-- p3 优惠减免项目: 优惠立减	优惠券金额	返利(活动退租金)	其他	金额合计
-- p2 订单总金额  支付方式
(
SELECT  r.* FROM
(
SELECT
-- p1:下单时间 订单状态 异常原因  城市 订单号 承租人 取车时间 还车时间 车品牌 车型 车牌号
ctriporder.createTime order_time,
-- 订单状态
case
when ctriporder.IsSubOrder =1 then '续租'
when ctriporder.IsSubOrder =0 ||ctriporder.IsSubOrder =false ||ISNULL(ctriporder.IsSubOrder) then
--      case
--      when LENGTH(ctriporder.pickupDateAct)>0 and LENGTH(ctriporder.returnDateAct)=0  then
-- 				 case
-- 				 when TIMESTAMPDIFF(HOUR ,pickupDate,pickupDateAct)>=3 then '异常'
-- 				 when TIMESTAMPDIFF(HOUR ,pickupDate,pickupDateAct)<=-3 then '异常'
-- 				 end
-- 		 when LENGTH(ctriporder.returnDateAct)>0 then
-- 				 case
-- 				 when TIMESTAMPDIFF(HOUR ,returnDate,returnDateAct)>=3 then '异常'
-- 				 when TIMESTAMPDIFF(HOUR ,returnDate,returnDateAct)<=-3 then '异常'
-- 				 else '正常'
-- 				 end
-- 		 when LENGTH(ctriporder.returnDateAct)=0 and ctriporder.`status` in (0,1,2,3) then '进行中'
--      when ctriporder.`status` = 99 then '正常'
--      end
       case
			 when ctriporder.`status` in (0,1,2,3) then
			     case
					 when TIMESTAMPDIFF(HOUR ,pickupDate,pickupDateAct)>=3 then '异常'
					 when TIMESTAMPDIFF(HOUR ,pickupDate,pickupDateAct)<=-3 then '异常'
					 else '进行中'
				 end
			 when ctriporder.`status` = 99 then
		       case
					 when TIMESTAMPDIFF(HOUR ,returnDate,returnDateAct)>=3 then '异常'
					 when TIMESTAMPDIFF(HOUR ,returnDate,returnDateAct)<=-3 then '异常'
					 else '正常'
			   end
		end
end  order_status,
-- 异常原因
case
when ctriporder.IsSubOrder =1 then ''
when ctriporder.IsSubOrder =0 ||ctriporder.IsSubOrder =false ||ISNULL(ctriporder.IsSubOrder) then
       case
			 when  ctriporder.`status` in (0,1,2,3) then
			     case
					 when TIMESTAMPDIFF(HOUR ,pickupDate,pickupDateAct)>=3 then '取车异常'
					 when TIMESTAMPDIFF(HOUR ,pickupDate,pickupDateAct)<=-3 then '取车异常'
					 else ''
					 end
			 when ctriporder.`status` = 99 then
		       case
					 when TIMESTAMPDIFF(HOUR ,returnDate,returnDateAct)>=3 then '还车异常'
					 when TIMESTAMPDIFF(HOUR ,returnDate,returnDateAct)<=-3 then '还车异常'
					 else ''
				   end
			 end
end  abnormal_reason,
-- 城市
city.CityName   city_name,
-- 订单号
ctriporder.orderNumberX order_number,
-- 承租人
ctriporder.userName lessee,
-- 取车时间
DATE_FORMAT(ctriporder.pickupDateAct,'%Y') pick_year,
DATE_FORMAT(ctriporder.pickupDateAct,'%m') pick_month,
DATE_FORMAT(ctriporder.pickupDateAct,'%d') pick_day,
-- 还车时间
DATE_FORMAT(ctriporder.returnDateAct,'%Y') return_year,
DATE_FORMAT(ctriporder.returnDateAct,'%m') return_month,
DATE_FORMAT(ctriporder.returnDateAct,'%d') return_day,
-- 车品牌 车型 车牌号
brand.`name`  car_brand,
carstore.CarType car_model,
carstore.CarNumber car_number,

-- p5 租金:天数 日租金 提前取车/延迟还车 退款/提前还车(退还客户金额) 金额合计
ctriporder.useDaysAct  order_days,
-- 日租金
FLOOR(ctriporder.carMoney/ctriporder.useDaysAct) order_rent_day,
-- 提前取车/延迟还车
CONCAT(ifnull(ctriporder.AdvanceGetFee,0.00),'/',ifnull(ctriporder.OvertimeFee,0.00)) order_rent_early_pickup,
-- 退款/提前还车(退还客户金额)
CONCAT(ifnull(ctriporder.ReturnCarRefund,0.00),'/',ifnull(ctriporder.ReturnCarRefund,0.00)) order_rent_refund,
-- 金额合计
ctriporder.carMoney order_rent_total,

-- p4 服务费: 尊享服务费  服务手续费	夜间服务费	取/送车服务费	异地取/还车	其他	金额合计
(ctriporder.BasicSecurityFee+ctriporder.PremiumServiceFee+ctriporder.XcEnjoymentService) order_service_fee_honour,
  -- 服务手续费
 ctriporder.serviceMoney order_service_fee_poundage,
 -- 夜间服务费
ctriporder.TipNightFee order_service_fee_night,
 -- 取/送车服务费
(ifnull(ctriporder.DeliveryFee,0.00)+ifnull(ctriporder.PickupFare,0.00)) order_service_fee_pick_return,
-- 异地取/还车
ifnull(ctriporder.PlacesFee,0.00) order_service_fee_another_place,
-- 其他
ifnull(ctriporder.AddOilFee,0.00) order_service_fee_other,
-- 金额合计
ctriporder.BasicSecurityFee,
ctriporder.PremiumServiceFee,
ctriporder.XcEnjoymentService,
ctriporder.serviceMoney,
ctriporder.DeliveryFee,
ctriporder.PickupFare,
ctriporder.PlacesFee,
ctriporder.TipNightFee,
ctriporder.AddOilFee,
(ctriporder.BasicSecurityFee+ctriporder.PremiumServiceFee+ctriporder.XcEnjoymentService+ctriporder.serviceMoney+ctriporder.DeliveryFee+
	ctriporder.PickupFare+ctriporder.PlacesFee+ctriporder.TipNightFee+ctriporder.AddOilFee) order_service_fee_total,

-- p3 优惠减免项目:  	优惠券金额	 	其他	金额合计
ifnull(ctriporder.discountMoney,0.00)  order_preferential_coupon,
ifnull(ctriporder.discountMoney,0.00) order_preferential_total,

-- p2 订单总金额  支付方式
ifnull(0.00,ctriporder.ActualAmount)   order_total_amount,
case
when ctriporder.pay_mode=1 then '门店付'
when ctriporder.pay_mode=2 then '在线付'
end pay_type

FROM xxx_xcctriporder ctriporder
LEFT JOIN xxx_city  city ON city.Id  = ctriporder.pickupCityId
LEFT JOIN xxx_carstore  carstore ON carstore.Id  = ctriporder.CarStoreKeyId
LEFT JOIN xxx_brand brand ON brand.Id  = carstore.BrandId
WHERE
1 = 1
-- AND ctriporder.`status`=99
AND ctriporder.isPay=1

-- 条件
-- AND ctriporder.createTime >=  '2021-01-01'
-- AND ctriporder.createTime <=  '2021-07-03'
{$where}
) as r
where
1 =1
{$where_status}
) as aa
sql;

        $data=  DB::connection('mysql_tripcar')->table(DB::raw($sql))->get()->toArray();