set @exesqloverdue =CONCAT('SELECT count(1) into@overdue_num FROM crm_worksheet a LEFT JOIN crm_worksheet_flow wf ON wf.id = a.worksheet_flow_id where now()>wf.handle_term_date and a.worksheet_state=3 and wf.handle_per= ',user_id,' '); IF begin_date is not null and begin_date !='' THEN set @exesqloverdue =CONCAT(@exesqloverdue ,' AND TO_DAYS(a.update_date) >= TO_DAYS(',begin_date,')'); //CONCAT 追加sql串 END IF;
IF end_date is not null and end_date !&#61;&#39;&#39; THEN set &#64;exesqloverdue &#61;CONCAT(&#64;exesqloverdue ,&#39; AND TO_DAYS(a.update_date) <&#61; TO_DAYS(&#39;,end_date,&#39;)&#39;); END IF; prepare overdue from &#64;exesqloverdue;//编译字符串 execute overdue;//运行字符串 SET overdue_num &#61; &#64;overdue_num ;//为返回参数赋值
set &#64;exesqlhandle &#61;CONCAT(&#39;SELECT count(1) into &#64;handle_num FROM crm_worksheet a LEFT JOIN crm_worksheet_flow wf ON wf.id &#61; a.worksheet_flow_id where a.worksheet_state&#61;3 and wf.handle_per&#61;&#39;,user_id,&#39;&#39; ); IF begin_date is not null and begin_date !&#61;&#39;&#39; THEN set &#64;exesqlhandle &#61;CONCAT(&#64;exesqlhandle ,&#39; AND TO_DAYS(a.update_date) >&#61; TO_DAYS(&#39;,begin_date,&#39;)&#39;); END IF;
IF end_date is not null and end_date !&#61;&#39;&#39; THEN set &#64;exesqlhandle &#61;CONCAT(&#64;exesqlhandle ,&#39; AND TO_DAYS(a.update_date) <&#61; TO_DAYS(&#39;,end_date,&#39;)&#39;); END IF;
prepare handle from &#64;exesqlhandle; execute handle; SET handle_num &#61; &#64;handle_num ;
set &#64;exesqlclaimNum &#61;CONCAT(&#39;SELECT count(1) into &#64;claim_num FROM crm_worksheet a LEFT JOIN crm_worksheet_flow wf ON wf.id &#61; a.worksheet_flow_id where a.worksheet_state&#61;2 and wf.handle_org&#61;&#39;,office_id,&#39;&#39;); IF begin_date is not null and begin_date !&#61;&#39;&#39; THEN set &#64;exesqlclaimNum &#61;CONCAT(&#64;exesqlclaimNum ,&#39; AND TO_DAYS(a.update_date) >&#61; TO_DAYS(&#39;,begin_date,&#39;)&#39;); END IF;
IF end_date is not null and end_date !&#61;&#39;&#39; THEN set &#64;exesqlclaimNum &#61;CONCAT(&#64;exesqlclaimNum ,&#39; AND TO_DAYS(a.update_date) <&#61; TO_DAYS(&#39;,end_date,&#39;)&#39;); END IF;
prepare claimNum from &#64;exesqlclaimNum; execute claimNum; SET claim_num &#61; &#64;claim_num ;
set &#64;exesqlalready &#61;CONCAT(&#39;select count(1) into &#64;already_handle_num from crm_worksheet_flow a where a.flow_cmd&#61;1 and a.handle_per is not null and a.handle_org is not null and a.handle_per !&#61;&#39;&#39; and a.handle_org !&#61;&#39;&#39; and a.handle_per&#61;&#39;,user_id,&#39;&#39;) ;
IF begin_date is not null and begin_date !&#61;&#39;&#39; THEN set &#64;exesqlalready &#61;CONCAT(&#64;exesqlalready ,&#39; AND TO_DAYS(a.update_date) >&#61; TO_DAYS(&#39;,begin_date,&#39;)&#39;); END IF;
IF end_date is not null and end_date !&#61;&#39;&#39; THEN set &#64;exesqlalready &#61;CONCAT(&#64;exesqlalready ,&#39; AND TO_DAYS(a.update_date) <&#61; TO_DAYS(&#39;,end_date,&#39;)&#39;); END IF;
prepare already from &#64;exesqlalready; execute already; SET already_handle_num &#61; &#64;already_handle_num ;
END
参数&#xff1a;
in begin_date varchar(64),in end_date varchar(64),in user_id varchar(64),in office_id varchar(64),out overdue_num int,out handle_num int,out claim_num int, out already_handle_num int