CREATE DEFINER=`root`@`%` PROCEDURE `sp_GetWangingsCount`(IN `PCode` VARCHAR(50), IN `FromDay` DATE, IN `Cycle` INT, IN `DayCount` INT, OUT `Rlt` INT, OUT `DayStr` VARCHAR(520))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ‘‘
BEGIN
if Date_Add(FromDay, interval DayCount-1 day) < date(now()) then
set Rlt = 0;
else
set @n = floor((datediff(date(now()), FromDay) + 1) / Cycle);
set Rlt = 0;
set DayStr = ‘‘;
set @i=1;
while @i <= @n do
set @_FromDay = Date_Add(FromDay, interval DayCount/Cycle*(@i-1) - 1 day) ;
if @_FromDay <= date(now()) then
if @i < @n then
set @_ToDay = Date_add(date(FromDay), interval DayCount/Cycle*@i day);
else
set @_ToDay = Date_add(date(Fromday), interval DayCount-1 day);
end if;
if datediff(@_ToDay, now()) > 0 then
set @_ToDay = date(now());
end if;
select @k:=if(sum(sa_money)/datediff(@_ToDay, @_FromDay) < 1000, 1 ,0)
from `t_sale`
where sa_code = PCode and sa_date between @_FromDay and @_ToDay;
set Rlt = Rlt + @k;
select @i, @_FromDay, @_ToDay, @k, Rlt;
if @k = 1 then
if DayStr = ‘‘ then
set DayStr = concat(DayStr, @_FromDay, ‘-‘, @_ToDay);
else
set DayStr = concat(DayStr, ‘,‘, @_FromDay, ‘-‘, @_ToDay);
end if;
end if;
end if;
set @i = @i + 1;
end while;
end if;
END
CREATE DEFINER=`root`@`%` PROCEDURE `showWarnings`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ‘ShowWarnigsDay‘
BEGIN
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
declare a_code varchar(50);
declare cur cursor for select * from `t_temp`;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
create table t_tmp
select
b.ps_name ps_name,
a.p_code p_code,
a.p_name p_name,
a.p_scale p_scale,
(select sum(c.sa_money) from t_sale c where c.sa_code=a.p_code) as sa_money,
p_scale-(select sum(c.sa_money) from t_sale c where c.sa_code=a.p_code) as sa_left,
datediff(p_EndDate,p_BeginDate) as dayLeft,
floor(datediff(a.p_EndDate, a.p_BeginDate)/7) as LeftWeek,
a.p_period p_period,
a.p_BeginDate p_eginDate,
(select sum(c.sa_money) from t_sale c where c.sa_code=a.p_code)/a.p_scale*100 As Percent,
1 as`预警次数` ,
1 as`破线次数` ,
‘2014-08-10‘ as`破线日期` ,
(select w_color from t_warning d where d.w_code=a.p_code) as W_color,
1 as `当日破线`
from t_product a
left join t_product_sort b on a.p_sort=b.ps_id and a.p_sort in (‘1‘,‘2‘);
-- 打开游标
OPEN cur;
-- 开始循环
read_loop: LOOP
-- 提取游标里的数据，这里只有一个，多个的话也一样；
FETCH cur INTO a_code;
-- 声明结束的时候
IF done THEN
LEAVE read_loop;
END IF;
call `spGetWangingsCount`(a_code, ‘‘, ‘‘, ‘‘);
update t_temp set `预警次数`=@;
END LOOP;
-- 关闭游标
CLOSE cur;
END