begin
declare m_dynamicInsertGsmNum int default 0;
declare carNum VARCHAR(10);
declare dri VARCHAR(20);
declare today double;
declare total double;
select count(*) into m_dynamicInsertGsmNum from m_dynamicinsert where gsmNum = new.gsmNum;
select car_num into carNum from m_car where gsm_num=new.gsmNum;
select driver into dri from m_car where gsm_num=new.gsmNum;
select sum(mileage) into today from m_lnglat where gsmNum = new.gsmNum and GPS_time between UNIX_TIMESTAMP(CAST(CURDATE() AS DATETIME)) and UNIX_TIMESTAMP(now());
select sum(mileage) into total from m_lnglat where gsmNum = new.gsmNum and GPS_time between UNIX_TIMESTAMP('2000/01/01 00:00:00') and UNIX_TIMESTAMP(now()) ;
if m_dynamicInsertGsmNum =0 then
insert into m_dynamicinsert(gsmNum, lng, lat, speed, gpsTime, carNum, driver, todayMileage, totalMileage) values( new.gsmNum, new.lng, new.lat, new.speed, new.GPS_time, carNum, dri, today, total);
else
update m_dynamicinsert set gsmNum=new.gsmNum , lng=new.lng , lat=new.lat , speed=new.speed , gpsTime=new.GPS_time, carNum=carNum, driver=dri, todayMileage=today, totalMileage=total where gsmNum= new.gsmNum;
end if;
end
declare m_dynamicInsertGsmNum int default 0;
declare carNum VARCHAR(10);
declare dri VARCHAR(20);
declare today double;
declare total double;
select count(*) into m_dynamicInsertGsmNum from m_dynamicinsert where gsmNum = new.gsmNum;
select car_num into carNum from m_car where gsm_num=new.gsmNum;
select driver into dri from m_car where gsm_num=new.gsmNum;
select sum(mileage) into today from m_lnglat where gsmNum = new.gsmNum and GPS_time between UNIX_TIMESTAMP(CAST(CURDATE() AS DATETIME)) and UNIX_TIMESTAMP(now());
select sum(mileage) into total from m_lnglat where gsmNum = new.gsmNum and GPS_time between UNIX_TIMESTAMP('2000/01/01 00:00:00') and UNIX_TIMESTAMP(now()) ;
if m_dynamicInsertGsmNum =0 then
insert into m_dynamicinsert(gsmNum, lng, lat, speed, gpsTime, carNum, driver, todayMileage, totalMileage) values( new.gsmNum, new.lng, new.lat, new.speed, new.GPS_time, carNum, dri, today, total);
else
update m_dynamicinsert set gsmNum=new.gsmNum , lng=new.lng , lat=new.lat , speed=new.speed , gpsTime=new.GPS_time, carNum=carNum, driver=dri, todayMileage=today, totalMileage=total where gsmNum= new.gsmNum;
end if;
end