MySQL从5.1.5开始,支持XML接口处理的两个函数: ExtractValue()和 UpdateXML().
需求:编写停车计费算法:
输入:Express Text(XML), BeginTime DateTime, EndTime DateTime;
输出:Decimal(10,2)-计费金额
计费分起步价和累计计费,分多个时段处理。 如果起步价跨越多个时段,则以第一个时段的价格为准。
<?xml version="1.0" encoding="utf-8"?> <ChargeExpress> <FreeCycle Unit="min" >15</FreeCycle> <ChargeCycle ID="peaktime" BeginTime="8:00:00" EndTime="20:00:00" > <MinFee Cycle="120" ChargeFee="0.08" ></MinFee> <NormalFee ChargeCycle="30" ChargeFee="0.04"></NormalFee> </ChargeCycle> <ChargeCycle ID="offpeaktime" BeginTime="20:00:00" EndTime="8:00:00" > <MinFee Cycle="120" ChargeFee="0.04" ></MinFee> <NormalFee ChargeCycle="30" ChargeFee="0.02"></NormalFee> </ChargeCycle> </ChargeExpress
MySQL Function:
CREATE FUNCTION `f_parking_meter`(`charge_scheme` text,`begin_time` datetime,`end_time` datetime) RETURNS decimal(10,2)
BEGIN
set @result=0.00;
set @parking_min=(UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(begin_time)) /60;
set @str_free_scheme_min=ExtractValue(charge_scheme, '/ChargeExpress/FreeCycle');
set @free_scheme_min=CAST(@str_free_scheme_min as UNSIGNED);
IF @parking_min <= @free_scheme_min THEN
RETURN @result;
END IF;
-- 总的计费方案节点数
set @total_chargecycle_node=ExtractValue(charge_scheme, 'count(/ChargeExpress/ChargeCycle/NormalFee)');
set @present_date=date(begin_time);
-- 是否正在计算起步价(1是,0否)
set @is_starting_price=1;
-- 起步价已经跨越到下个计费时段(这里假定最多跨越2个,不会再跨越到第三个时段
set @is_cross_period_starting_price = 0;
set @is_done=0;
while not @is_done do
set @currentNodeCounter=0;
while @currentNodeCounter < @total_chargecycle_node do
set @currentNodeCounter=@currentNodeCounter+1;
set @nodeInfo = concat('/ChargeExpress[1]/ChargeCycle[',@currentNodeCounter,']');
set @node_begin_time=ExtractValue(charge_scheme, concat(@nodeInfo,'/@BeginTime'));
set @node_end_time=ExtractValue(charge_scheme, concat(@nodeInfo,'/@EndTime'));
set @str_minFee_Cycle=ExtractValue(charge_scheme, concat(@nodeInfo,'/MinFee/@Cycle'));
set @minFee_Cycle=CAST(@str_minFee_Cycle as UNSIGNED);
set @minFee_ChargeFee=ExtractValue(charge_scheme, concat(@nodeInfo,'/MinFee/@ChargeFee'));
set @str_NormalFee_ChargeCycle=ExtractValue(charge_scheme, concat(@nodeInfo,'/NormalFee/@ChargeCycle'));
set @normalFee_ChargeCycle=CAST(@str_NormalFee_ChargeCycle as UNSIGNED);
set @normalFee_ChargeFee=ExtractValue(charge_scheme, concat(@nodeInfo,'/NormalFee/@ChargeFee'));
set @schema_start_time=STR_TO_DATE(concat(@present_date,' ', @node_begin_time),'%Y-%m-%d %H:%i:%s');
set @schema_end_time=STR_TO_DATE(concat(@present_date,' ', @node_end_time),'%Y-%m-%d %H:%i:%s');
if time(@node_end_time) <= time(@node_begin_time) then
set @schema_end_time = DATE_ADD(@schema_end_time, INTERVAL 1 DAY);
end if;
if @schema_start_time >= end_time then
set @is_done=1;
else
if not (@schema_end_time <= begin_time) then
-- insert into memory_scheme select @schema_start_time,@schema_end_time,@minFee_Cycle,@minFee_ChargeFee,@normalFee_ChargeCycle,@normalFee_ChargeFee;
if @is_starting_price = 1 then
-- 正在计算起步价
if @parking_min <= @minFee_Cycle then
RETURN @minFee_ChargeFee;
else
set @stop_dot=IF(end_time >= @schema_end_time, @schema_end_time, end_time);
set @parking_duration=(UNIX_TIMESTAMP(@stop_dot) - UNIX_TIMESTAMP(begin_time)) /60;
--
if @parking_duration >= @minFee_Cycle then
set @result = @minFee_ChargeFee + ceil((@parking_duration-@minFee_Cycle)/@normalFee_ChargeCycle)*@normalFee_ChargeFee;
else
set @result = @minFee_ChargeFee;
set @is_cross_period_starting_price = 1;
set @starting_price_left_min=@minFee_Cycle-@parking_duration;
end if;
set @is_starting_price = 0;
end if;
else
-- 已经算过起步价了
set @stop_dot=IF(end_time >= @schema_end_time, @schema_end_time, end_time);
if @is_cross_period_starting_price = 0 then
set @parking_duration=(UNIX_TIMESTAMP(@stop_dot) - UNIX_TIMESTAMP(@schema_start_time)) /60;
set @result = @result + ceil(@parking_duration/@normalFee_ChargeCycle)*@normalFee_ChargeFee;
else
set @parking_duration=(UNIX_TIMESTAMP(@stop_dot) - UNIX_TIMESTAMP(@schema_start_time)) /60 - @starting_price_left_min ;
set @result = @result + ceil(@parking_duration/@normalFee_ChargeCycle)*@normalFee_ChargeFee;
end if;
end if;
end if;
end if;
end while;
set @present_date=DATE_ADD(@present_date, INTERVAL 1 DAY);
end while;
RETURN @result;
END
Test case
BeginTime | 2016/1/19 19:17:58 | ||||
EndTime | 2016/1/19 22:25:13 | ||||
开始时间 | 结束时间 | 计费类型 | 计费单元 | 单元单价 | 金额 |
2016/1/19 19:17 | 2016/1/19 21:17 | 起价 | 1 | 0.08 | 0.08 |
2016/1/19 21:17 | 2016/1/19 22:25 | 夜间 | 3 | 0.02 | 0.06 |
合计 | 0.14 | ||||
函数输出 | 0.14 | ||||
|
BeginTime | 2016/1/17 11:26:05 | ||||
EndTime | 2016/1/19 15:40:26 | ||||
开始时间 | 结束时间 | 计费类型 | 计费单元 | 单元单价 | 金额 |
2016/1/17 11:26 | 2016/1/17 13:26 | 起价 | 1 | 0.08 | 0.08 |
2016/1/17 13:26 | 2016/1/17 20:00 | 白天 | 14 | 0.04 | 0.56 |
2016/1/17 20:00 | 2016/1/18 8:00 | 夜间 | 24 | 0.02 | 0.48 |
2016/1/18 8:00 | 2016/1/18 20:00 | 白天 | 24 | 0.04 | 0.96 |
2016/1/18 20:00 | 2016/1/19 8:00 | 夜间 | 24 | 0.02 | 0.48 |
2016/1/19 8:00 | 2016/1/19 15:40 | 白天 | 16 | 0.04 | 0.64 |
合计 | 3.20 | ||||
函数输出 | 3.20 | ||||
|