/********创建一个存储过程用来刷卡进入，传入参数为卡号，传出参数1,表示成功，-1表示卡不存在,-2表示无卡对应的车**********/
drop procedure `code`.`enterparking`;
DELIMITER $$
CREATE    PROCEDURE `code`.`enterparking`(in carNo int ,out  flag int)
BEGIN
declare cId int; /*卡id*/
declare carId int ;/*车id*/
declare carState int;/*车辆状态*/
/*查卡表**/
select c_id into cId from cards where card_id=carNo and state=1 and end_time >now() limit 1;  /**查询卡id*/
if (cId is not null) then
begin
/*查车辆管理表*/
select car_id ,car_state into carId ,carState from car_manage where c_id=cId and car_time>now() limit 1;/**查询车id*/
if (carId is not null) then
if(carState=2) then/*carState=2表示为此车为空闲状态*/
begin
/**修改车辆进出明细表**/
insert into  car_details values(null,carId,now(),null,null);
/**修改车辆为停车**/
update car_manage set car_state=1 where car_id=carId;
set flag=1;
end;
else
select 1;
set flag=-3;/**出现异常，此车为停车状态,原因是上次出来的时候没刷卡*/
end if;
else
select 2;
set flag=-2;/**卡号对应的车辆不存在*/
end if;
end;
else
select 3;
set flag=-1;/**卡号不存在*/
end if;
END$$
DELIMITER ;
call enterparking(11223334,@a);
select @a;

/********创建一个存储过程用来刷卡出结算，传入参数为卡号，传出参数为钱**********/
drop PROCEDURE `code`.`outparking`
DELIMITER $$
CREATE    PROCEDURE `code`.`outparking`(in carNo int ,out  money int)
BEGIN
declare cId int; /*卡id*/
declare carId int ;/*车id*/
declare ismonth int ;/*是否包月*/
declare carTypeId int ;/*车辆类型id**/
declare cardMoney int ;/*卡原先余额*/
declare indate datetime;/*进*/
declare outdate datetime;/**出*/
/*查卡表**/
select c_id, code_moneys into cId,cardMoney from cards where card_id=carNo and state=1 and end_time >now() limit 1;  /**查询卡id*/
if (cId is not null ) then
begin
/*查车辆管理表*/
select car_id ,months,car_typeid into carId ,ismonth, carTypeId from car_manage where c_id=cId and car_time>now() limit 1;/**查询车id*/
if carId is not null then
begin
if(ismonth=1) then  /**包月,*/
begin
/*查车辆类型表,得到包月费，这里按月扣款**/
select month_moneys into money from car_types where car_typeid=carTypeId;
/**得到进入时间*/
select max(come_times) into indate from car_details where car_id=carId and come_times is not NULL and leave_times is NULL;
if(indate is not NULl) then
begin
if(cardMoney>money) then
begin
declare  begin_time datetime;/**定义一个变量用来存储包月时间*/
declare time1 varchar(20);/*当天时间：eg：23:05:05*/
declare time2 varchar(20);
declare  flag int default 0;/*1表示已经扣了本月款,0表示本月不必再扣*/
/*得到出的时间*/
select now() into outdate;
/*得到包月的开始时间*/
select begin_time into begin_time from car_manage car_manage where  car_id=carId;
select RIGHT(outdate,8) into time1;
select RIGHT(indate,8) into time2;
if(((SELECT  DATEDIFF(outdate,indate)%30=0)&&(time1=time2))=1) then /*一个月的周期开始扣钱**/
begin
update cards set code_moneys=code_moneys-money where card_id=carNo;
update car_details set moneys=money where car_id=carId and come_times=indate;/*把扣钱添入明细表*/
set flag=1;
end;
end if;
/*添加出入明细*/
update car_details set leave_times=outdate where car_id=carId and come_times=indate;
/*修改车的状态为空闲*/
update car_manage set car_state=2 where car_id=carId;
if(flag=0) then
set money=0;
end if;
end;
else
set money=-4;/**余额不足*/
select 1;
end if;
end;
else
set money=-3;/**出现异常,没有进的记录或已经刷卡，非法进入*/
select 2;
end if;
end;
else            /**散客*/
begin
declare per int ;/*每小时收取的费用*/
select car_moneys into per from car_types where car_typeid=carTypeId;
/**得到进入时间*/
select max(come_times) into indate from car_details where car_id=carId and come_times is not NULL and leave_times is NULL;
if(indate is not NULl) then
begin
/*得到出时间*/
select now() into outdate;
/**算出该扣的总金额,money*/
select ceiling((time_to_sec(outdate)-time_to_sec(indate))/1800)*per into money;/*得到收取的钱*/
if(cardMoney>money) then
begin
/**修改车辆进出明细表**/
update car_details set leave_times=outdate, moneys=money where car_id=carId and come_times=indate;
/**修改车辆为空闲**/
update car_manage set car_state=2 where car_id=carId;
/*扣钱*/
update cards set code_moneys=code_moneys-money where card_id=carNo;
end;
else
set money=-4;/**余额不足*/
select 3;
end if;
end;
else
set money=-3;/**出现异常,没有进的记录或已经刷卡，非法进入*/
select 4;
end if;
end;
end if;
end;
else
select 5;
set money=-2;/**卡号对应的车辆不存在*/
end if;
end;
else
select 6;
set money=-1;/**卡号不存在*/
end if;
END$$
DELIMITER ;

call outparking(11223334,@b);
select @b;