$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();
laravel长sql查询
于 2021-10-14 16:09:48 首次发布
该SQL查询用于分析租车订单的异常状态(如取车、还车时间异常)、订单费用详情(包括租金、服务费、优惠减免等),并提供了订单的创建时间、城市、承租人信息以及各种费用的计算。查询结果将按订单状态、异常原因、城市、订单号等信息展示,并区分了在线与门店支付方式。
摘要由CSDN通过智能技术生成