create definer = 'root'@'%' procedure `proc_getpatientcondition`(
in ipatientid integer(11),
in dstartdate date,
in denddate date
)
not deterministic
contains sql
sql security definer
comment ''
begin
declare tempdate date;
declare tempdaybegin varchar(9);
declare tempnightbegin varchar(9);
declare tempdaytimes int;
declare tempnighttimes int;
declare tempfiledbrate int;
declare tempresultlevel int;/* 1,2,3,4 ,when 0 means no result */
declare tempresultbyflag int; /* 1-by day times per week , 2- by night times per month , 3- by filedbrate */
declare tempisfinish int;
declare vsql varchar(2000);
declare stmt varchar(2100);
declare tempstartdateweekday int;
declare tempweekindex int ;
set tempdaytimes = 0;
set tempnighttimes = 0;
set tempfiledbrate = 0;
set tempresultlevel = 0;
set tempresultbyflag = 0;
set tempisfinish = 0;
/* get the day time begin */
select concat(' ',ifnull(paravalue,'07:00'),':00') into tempdaybegin from dbparameter
where parakey='daytime';
select concat(' ',ifnull(paravalue,'20:00'),':00') into tempnightbegin from dbparameter
where parakey='nighttime';
/* define temp table to contains all report dates */
drop temporary table if exists temp_tb_dates;
create temporary table temp_tb_dates
(reportdate date
,theweek varchar(7)
,themonth varchar(7)
);
drop temporary table if exists temp_tb_diarysumm;
create temporary table temp_tb_diarysumm
,daytimes int
,nighttimes int
,filedbrate int
set tempdate = dstartdate;
set tempstartdateweekday = weekday(dstartdate); /* 0-monday */
set tempweekindex = 1;
while tempdate <= denddate do
insert into temp_tb_dates(reportdate,theweek,themonth)
value(tempdate, tempweekindex ,date_format(tempdate,'%y-%m') );
if tempstartdateweekday = 6 then
set tempstartdateweekday = 0;
set tempweekindex = tempweekindex + 1;
else
set tempstartdateweekday = tempstartdateweekday + 1;
end if;
set tempdate = date_add(tempdate, interval 1 day);
end while;
set vsql = ' ';
set vsql=concat(vsql,' insert into temp_tb_diarysumm');
set vsql=concat(vsql,' select * from ');
set vsql=concat(vsql,' ( ');
set vsql=concat(vsql,' select t0.reportdate ,theweek , themonth ');
set vsql=concat(vsql,' , ifnull(daytimes,0) as daytimes ');
set vsql=concat(vsql,' , ifnull(filedbrate,0) as filedbrate ');
set vsql=concat(vsql,' from ');
set vsql=concat(vsql,' select reportdate ,theweek , themonth ');
set vsql=concat(vsql,' from temp_tb_dates ');
set vsql=concat(vsql,' ) t0 ');
set vsql=concat(vsql,' left join ');
set vsql=concat(vsql,' select count(recordid) as daytimes ');
set vsql=concat(vsql,' ,date_format(filedatime,''%y-%m-%d'') as reportdate ');
set vsql=concat(vsql,' from dbdiaryfileda ');
set vsql=concat(vsql,' where patientid=', ipatientid);
set vsql=concat(vsql,' and filedatime >=','', 'concat(date_format(filedatime,''%y-%m-%d''),''',tempdaybegin,''')');
set vsql=concat(vsql,' and filedatime < ','', 'concat(date_format(filedatime,''%y-%m-%d''),''',tempnightbegin ,''')');
set vsql=concat(vsql,' group by reportdate ) t1 ');
set vsql=concat(vsql,' on t1.reportdate = t0.reportdate ');
set vsql=concat(vsql,' select ((ifnull(max(filedb),1) - ifnull(min(filedb),1)) * 200 ) /( ifnull(max(filedb),1) + ifnull(min(filedb),1) ) as filedbrate ');
set vsql=concat(vsql,' ,date_format(gathertime,''%y-%m-%d'') as reportdate ');
set vsql=concat(vsql,' from dbdiaryfiledbb ');
set vsql=concat(vsql,' and favailflag > 0 ');
set vsql=concat(vsql,' and gathertime >= ','','concat(date_format(gathertime,''%y-%m-%d''),''',' 00:00:00',''')');
set vsql=concat(vsql,' and gathertime < ','','concat(date_format(date_add(gathertime, interval 1 day),''%y-%m-%d''),''',' 00:00:00',''')');
set vsql=concat(vsql,' group by reportdate ) t3 ');
set vsql=concat(vsql,' on t3.reportdate = t0.reportdate ');
set vsql=concat(vsql,' ) t ');
set @sqltext:=vsql;
prepare stmt from @sqltext;
execute stmt;
select max(daytimesweek) into tempdaytimes
from
(
select sum(daytimes) as daytimesweek
from temp_tb_diarysumm
group by theweek
) tday;
select max(nighttimesmonth) into tempnighttimes
select sum(nighttimes) as nighttimesmonth
group by themonth
) tnight;
select max(filedbrate) into tempfiledbrate
from temp_tb_diarysumm;
if tempisfinish=0 and tempfiledbrate > 30 then
set tempisfinish = 1;
set tempresultbyflag =3;
set tempresultlevel = 3;
end if;
if tempisfinish=0 and tempfiledbrate <= 30 and tempfiledbrate >= 20 then
set tempresultlevel = 2;
if tempisfinish=0 and tempfiledbrate < 20 and tempfiledbrate > 0 then
set tempresultlevel = 1;
select tempresultlevel , tempresultbyflag , tempdaytimes, tempnighttimes ,tempfiledbrate;
end;