MySQL的XML接口-数据解析

      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