create or replace procedure proc_SmsSendQueue as
tmpMobilePhone varchar2(50);
tmpSendcontent NVARCHAR2(100);
tmpPhoneType CHAR(2);
tmpSmsType VARCHAR2(16);
tmpPriority Number(1);
tmpEnterTime Date;
tmpSendFlag Char(1);
tmpSendCount Number(1);
tmpCreatedby Number(8);
tmpCreatedon Date;
tmpLastupdatedby Number(8);
tmpLastupdatedon Date;
tmpSendTasksn number(16);
tmpSMSSENDSN NUMBER(16);
tmpsmssenddsn number(16);
s_processsn number(16);
s_flag char(1);
s_count number(8);
starttime date;
remark varchar2(100);
cursor cur_SmsSendQueue is
select sendtasksn,sendtaskdtlsn,mobilephone,Sendcontent,phonetype,'促销',tmpPriority,sysdate,'0',tmpSendCount,17,sysdate,17,sysdate from vcrm_sms_sendcustomer2 where sendtasksn=tmpSendTasksn and length(mobilephone)=11;
begin
starttime:=sysdate;
select flag into s_flag from plus.TProcess where Procedure = 'proc_SmsSendQueue';
select processsn into s_processsn from plus.TProcess where Procedure = 'proc_SmsSendQueue';
if s_flag='0' then
update plus.TProcess set flag='1',lastupdatedon=sysdate where processsn=s_processsn;
commit;
--Find how many records from crm_sms_sendtask where flag='2'
select count(*) into s_count from crm_sms_sendtask where flag='2' and senddate<sysdate and senddate>=sysdate-7;
if s_count>0 then
-- Search sendtasksn from crm_sms_sendtask
select sendtasksn into tmpSendTasksn from (select sendtasksn from crm_sms_sendtask where flag='2' and senddate<sysdate order by senddate) where rownum<2;
--Find how many records from crm_sms_sendcustomer where sendtasksn=tmpsendtasksn
select count(*) into s_count from crm_sms_sendcustomer where sendtasksn=tmpSendTasksn;
if s_count>0 then
--Search Priority from crm_sms_TypePriority
select priority into tmpPriority from crm_sms_TypePriority where SmsType='促销';
--Search SendCount from crm_sms_TypePriority
select SendCount into tmpSendCount from crm_sms_TypePriority where SmsType='促销';
--open cursor for insert crm_sms_SendQueue
open cur_SmsSendQueue;
loop
fetch cur_SmsSendQueue into tmpSMSSENDSN,tmpsmssenddsn,tmpMobilePhone,tmpSendcontent,tmpPhoneType,tmpSmsType,tmpPriority,tmpEnterTime,tmpSendFlag,tmpSendCount,tmpCreatedby,tmpCreatedon,tmpLastupdatedby,tmpLastupdatedon;
exit when cur_SmsSendQueue%notfound;
remark :='短信发送任务导入到短信发送队列表'||tmpSendTasksn;
begin
insert into crm_sms_SendQueue (SmsSendSn,SmsSenddSn,MobilePhone,sendcontent,PhoneType,SmsType,priority,EnterTime,SendFlag,SendCount,Createdby,Createdon,Lastupdatedby,lastupdatedon)
values (tmpSMSSENDSN,tmpsmssenddsn,tmpMobilePhone,tmpSendcontent,tmpPhoneType,tmpSmsType,tmpPriority,tmpEnterTime,tmpSendFlag,tmpSendCount,tmpCreatedby,tmpCreatedon,tmpLastupdatedby,tmpLastupdatedon);
commit;
exception
when others then
update crm_sms_sendtask set flag='7',lastupdatedon=sysdate,lastupdatedby=17 where sendtasksn=tmpSendTasksn;
commit;
end;
end loop;
close cur_smsSendQueue;
update crm_sms_sendtask set flag='4',lastupdatedon=sysdate,lastupdatedby=17 where sendtasksn=tmpSendTasksn;
commit;
else
update crm_sms_sendtask set flag='7',lastupdatedon=sysdate,lastupdatedby=17 where sendtasksn=tmpSendTasksn;
commit;
remark:=remark||'短信发送任务中没有明细';
end if;
else
remark:=remark||',短信发送任务中没有通过的任务';
end if;
else
remark:=remark||',进程初始化失败';
end if;
insert into plus.tprocess_log (ProcessSN,Startupby,Startupon,Stopedby,Stopedon,Remark,Lastupdatedby,Lastupdatedon,Flag)
values (s_processsn,
17,
starttime,
17,
sysdate,
remark,
17,
sysdate,
s_flag
);
commit;
update plus.TProcess set flag='0',lastupdatedon=sysdate where processsn=s_processsn;
commit;
end;