存储过程 正式课和迈向课的

ESI_QIANDAO4MAIXIANG

CREATE OR REPLACE PROCEDURE "ESI_QIANDAO5MAIXIANG" (qdQRcode in varchar2, -- 签到二维码
out_Course out varchar2, -- 课程期次
out_Student out varchar2, -- 学员姓名
out_Group out varchar2, -- 所属小组
out_Tutor out varchar2, -- 助教老师
out_Flag out int, -- 是否成功flag
out_sqlcode out int,
out_sqlerrm out varchar2,
in_UserID in int, -- 用户id
in_Status in int,
out_SGroup out varchar2, -- 大组
out_Customer out varchar2,
in_AppID in varchar2) AS
-- 客户名称

qdID varchar2(22); --签到ID
xxjhID varchar2(22); --学习计划ID
jxjhID varchar2(22); --教学计划ID
ddID varchar2(22); --订单ID
xxjhPlanNumber int; -- 学习计划表中计划人数
xxjhAlreadyCourse int; -- 学习计划中已签到人数
qdAccountid int; -- 客户ID
qdAccountBaohuYigou int; -- 客户池：保护池/已购池
ddIDCreateTime varchar2(22); -- 签到表对应订单的创建时间
khStatePrew int; -- 客户原来的状态(更新前的状态)

out_unpayed_amount int; -- 订单中未收款金额

out_org_id varchar2(22);
out_yckb_id int;

in_the_AppID varchar2(22); --签到的AppID

out_the_Flag int; --先查询二维码是否过期，即以签到表对应的教学计划中课程的开始和结束日期作为条件

begin
begin

/* 
----------------------------------------------------------------------
先进行7项检查
1 非法的二维码 
2 签到二维码已过期 
3 未到签到时间 
4 签到状态为是,即已签到
5 签到表类型为正式课
6 签到表的参课判定不是参加
7 未收款金额是否>0, >0则需要先收款，才可以签到
----------------------------------------------------------------------
*/

-- 1 非法的二维码
select count(yckb_id)
into out_the_Flag
from tcu_yckb
where qrcode = qdQRcode;

if out_the_Flag = 0 then
out_the_Flag := null;
goto InvalidQrCode;
end if;

-- 2 签到表类型为正式课
SELECT count(t.yckb_id)
into out_the_Flag
FROM tcu_yckb t
inner join tc_contact tc
on t.yckb_refid01 = tc.contact_id
and tc.org_id = t.org_id
inner join tcu_jiaoxplan jp
on t.yckb_refid02 = jp.jiaoxplan_id
and t.org_id = jp.org_id
inner join tc_account khb
on tc.account_id = khb.account_id
and khb.org_id = tc.org_id
where t.yckb_type = 2132 -- 迈向课
and t.qrcode = qdQRcode;

if out_the_Flag = 0 then
out_the_Flag := null;
goto TheQrCodeTypeIsZhengShi;
end if;

-- 3 签到二维码已过期
SELECT count(t.yckb_id)
into out_the_Flag
FROM tcu_yckb t
inner join tc_contact tc
on t.yckb_refid01 = tc.contact_id
and tc.org_id = t.org_id
inner join tcu_jiaoxplan jp
on t.yckb_refid02 = jp.jiaoxplan_id
and t.org_id = jp.org_id
inner join tc_account khb
on tc.account_id = khb.account_id
and khb.org_id = tc.org_id
where (trunc(jp.jiaoxplan_date02 + 1) - 1 / 24 / 60 / 60) < sysdate -- 当前时间>结课日期的23:59:59
and t.qrcode = qdQRcode;

if out_the_Flag = 1 then
out_the_Flag := null;
goto TheCourceIsOver;
end if;

-- 4 未到签到时间
SELECT count(t.yckb_id)
into out_the_Flag
FROM tcu_yckb t
inner join tc_contact tc
on t.yckb_refid01 = tc.contact_id
and t.org_id = tc.org_id
inner join tcu_jiaoxplan jp
on t.yckb_refid02 = jp.jiaoxplan_id
and t.org_id = jp.org_id
inner join tc_account khb
on tc.account_id = khb.account_id
and tc.org_id = khb.org_id
where jp.jiaoxplan_date01 - 1 > trunc(sysdate) -- 当前时间<报到日(开课日期前一天)0点,即未到可签到时间
and t.qrcode = qdQRcode;

if out_the_Flag = 1 then
out_the_Flag := null;
goto CantQianDao;
end if;

-- 5 签到状态为是,即已签到 
SELECT count(t.yckb_id)
into out_the_Flag
FROM tcu_yckb t
inner join tc_contact tc
on t.yckb_refid01 = tc.contact_id
and tc.org_id = t.org_id
inner join tcu_jiaoxplan jp
on t.yckb_refid02 = jp.jiaoxplan_id
and t.org_id = jp.org_id
inner join tc_account khb
on tc.account_id = khb.account_id
and khb.org_id = tc.org_id
where t.yckb_int03 = 1001 -- 签到状态：否
and t.qrcode = qdQRcode;

if out_the_Flag = 0 then
out_the_Flag := null;
goto TheQrCodeAlreadySigned;
end if;

-- 6 签到表的参课判定不是参加
SELECT count(t.yckb_id)
into out_the_Flag
FROM tcu_yckb t
inner join tc_contact tc
on t.yckb_refid01 = tc.contact_id
and tc.org_id = t.org_id
inner join tcu_jiaoxplan jp
on t.yckb_refid02 = jp.jiaoxplan_id
and t.org_id = jp.org_id
inner join tc_account khb
on tc.account_id = khb.account_id
and khb.org_id = tc.org_id
where t.yckb_int05 = 1001 -- 参课判定:参加
and t.qrcode = qdQRcode;

if out_the_Flag = 0 then
out_the_Flag := null;
goto TheQrCodeTypeIsnotCanjia;
end if;

-- 7 查询未收款金额
select contract_unpayed_amount
into out_unpayed_amount
from tc_contract
where contract_id =
(select ref_3 from tcu_yckb where qrcode = qdQRcode);

-- 未收款金额>0 ，需要先缴费
if out_unpayed_amount > 0 then
out_the_Flag := null;
goto PayFirst;
end if;

/* 

改造选“没查到符合条件的签到表”中的逻辑，继续细化成几种情况
没查到符合条件的签到表,
可能情况：
1 签到表的参课判定为 不参加; 
2 签到表的类型为：迈向课签到表；
3 签到表的签到状态为：是

SELECT count(t.yckb_id)
into out_the_Flag
FROM tcu_yckb t
inner join tc_contact tc
on t.yckb_refid01 = tc.contact_id
and tc.org_id = t.org_id
inner join tcu_jiaoxplan jp
on t.yckb_refid02 = jp.jiaoxplan_id
and t.org_id = jp.org_id
inner join tc_account khb
on tc.account_id = khb.account_id
and khb.org_id = tc.org_id
where
-- jp.jiaoxplan_date01 >= trunc(sysdate)
-- and (trunc(jp.jiaoxplan_date02 + 1) - 1 / 24 / 60 / 60) > sysdate

-- 当前日期>=报到日(开课日期前一天)0点,其当前日期<=结课日期的23:59:59
jp.jiaoxplan_date01 - 1 <= trunc(sysdate)
and trunc(jp.jiaoxplan_date02 + 1) - 1 / 24 / 60 / 60 >= sysdate
and t.yckb_int03 = 1001 -- 签到状态：否
-- and t.yckb_type = 2136 -- 正式课
and t.yckb_int05 = 1001 -- 参课判定:参加
and t.qrcode = qdQRcode;

-- 没查到符合条件的签到表
if out_the_Flag = 0 then
out_the_Flag := null;
goto NoRecordddd;
end if;

*/

-- 1 签到动作本身
-- 获取签到表相关信息
-- 获取签到表ID
select tb.yckb_id,
tb.ref_3,
tb.yckb_refid02,
tb.account_id,
tc.create_time -- 签到表对应订单的创建时间
into qdID, ddID, jxjhID, qdAccountid，ddIDCreateTime
from tcu_yckb tb
inner join tc_contract tc
on tb.ref_3 = tc.contract_id
where tb.qrcode = qdQRcode;

-- 获取学习计划ID
-- 先查询
SELECT count(t.ref_1)
into out_the_Flag
from tcu_yckb_1_1 t
where yckb_id = qdID;

if out_the_Flag = 0 then
goto NoXueXiPlan;
end if;

-- 再获取
select ref_1 into xxjhID from tcu_yckb_1_1 where yckb_id = qdID;

-- 更新签到表 更新字段：是否签到,有效到场/合格总裁,签到时间,是否电子签到
update tcu_yckb
set yckb_int03 = '1001',
-- set yckb_int03 = '1002', -- lgy eidt @2015-5-12 for 可以多次签到
yckb_int06 = '1002', -- 先默认是否，签到更新的时候再更新此字段
yckb_date01 = sysdate - 1 / 3,
IsESI = 1
where yckb_id = qdID;

-- 更新签到表扩展表 更新字段：yckb_refid06 签到人
update tcu_yckb_xattr
set yckb_refid06 = in_UserID
where yckb_id = qdID;

-- 更新学习计划表
update tcu_learnplan
set learnplan_dec04 = learnplan_dec04 + 1,
learnplan_dec05 = learnplan_dec05 - 1
where learnplan_id = xxjhID;
-- 获取学习计划中的计划人数，已签到人数
select learnplan_dec04, learnplan_dec01
into xxjhPlanNumber, xxjhAlreadyCourse
from tcu_learnplan
where learnplan_id = xxjhID;
-- 如果学习计划中的计划人数=已签到人数，则此学习计划的状态设置为完成
if (xxjhPlanNumber = xxjhAlreadyCourse) then
update tcu_learnplan
set learnplan_int03 = 1003
where learnplan_id = xxjhID;
end if;

-- 更新订单表
update tc_contract tct
set cntrt_dec03 = cntrt_dec03 + 1, cntrt_dec09 = cntrt_dec09 - 1
where tct.contract_id = ddID;

-- 更新客户状态 :如果客户所在客户池，不是已购池，则 1 把客户状态改为：迈向签到状态 2 记录客户状态变更表
select acct_int02, acct_int03
into qdAccountBaohuYigou, khStatePrew
from tc_account
where account_id = qdAccountid;

if (qdAccountBaohuYigou <> 1004) then
-- 1 更改客户状态
update tc_account
set acct_int03 = 1010
where account_id = qdAccountid;
-- 2 写更改客户状态日志表及相关
-- 2.1 写更改客户状态日志表
insert into tcu_cuschange
(ORG_ID,
cuschange_id,
account_id,
owner_user_id,
create_user_id,
create_time,
modify_user_id,
modify_time,
dept_id,
cuschange_int03,
cuschange_int04)
values
(1,
(select max(cuschange_id) + 1 from tcu_cuschange),
qdAccountid,
in_UserID,
in_UserID,
ddIDCreateTime, -- 签到表对应订单的创建时间
in_UserID,
sysdate,
(select dept_id from tc_user where user_id = in_UserID), -- 用户所在的部门ID
khStatePrew,
1010);

-- 2.2 写更改客户状态日志表 tcu_cuschange_attr 表
insert into tcu_cuschange_attr
(org_id, cuschange_ID, cuschange_refid03, cuschange_refid04)
values
(1, (select max(cuschange_id) from tcu_cuschange), in_UserID, 0);

-- 2.3 写更改客户状态日志表 tcu_cuschange_xattr 表 
insert into tcu_cuschange_xattr
(org_id,
cuschange_ID,
cuschange_refid05,
cuschange_refid06,
cuschange_refid07,
cuschange_refid08)
values
(1, (select max(cuschange_id) from tcu_cuschange), 0, 0, 0, 0);

end if;

-- 获取要返回的信息
SELECT tat.account_name,
t.yckb_int01, -- 大组
tc.contact_name,
ub.user_name,
-- substr(to_char(t.yckb_int09), 3, 2),
t.yckb_int09, -- 小组
jp.jiaoxplan_name,
t.org_id,
t.yckb_id
into out_Customer,
out_SGroup,
out_Student,
out_Tutor,
out_Group,
out_Course,
out_org_id,
out_yckb_id
FROM tcu_yckb t
left join tc_account tat
on tat.account_id = t.account_id
left join tcu_yckb_attr b
on t.yckb_id = b.yckb_id
and b.org_id = t.org_id
left join tcu_yckb_1_1 c
on t.yckb_id = c.yckb_id
and c.org_id = t.org_id
left join tc_user ub
on b.yckb_refid04 = ub.user_id
and ub.org_id = b.org_id
left join tc_contact tc
on t.yckb_refid01 = tc.contact_id
and tc.org_id = t.org_id
left join tcu_jiaoxplan jp
on t.yckb_refid02 = jp.jiaoxplan_id
and jp.org_id = t.org_id
WHERE QRCODE = qdQRcode
and t.yckb_id = qdID
and rownum = 1;

-- 得到大组信息 
if out_SGroup <> '0' then
select distinct enum_value
into out_SGroup
from tc_enum_str
where attr_name = 'yckb.newdazu'
and enum_key = out_SGroup
order by enum_key desc;
end if;

-- 得到小组信息 
if out_Group <> '0' then
out_Group := substr(out_Group, 3, 2);
end if;

-- 获取到实际的appID
select appid into in_the_AppID from ESI_Client where authno = in_AppID;
-- 2 写签到日志表

insert into esi_signinlog
(org_id,
yckb_id,
appid,
userid,
qrcode,
course,
student,
tutor,
learninggroup,
signintime,
status)
values
(out_org_id,
out_yckb_id,
in_the_AppID,
in_UserID,
qdQRcode,
out_Course,
out_Student,
out_Tutor,
out_Group,
sysdate() - 1 / 3,
in_Status);

commit;
out_Flag := 100;
exception
when others then

out_sqlcode := sqlcode;
out_sqlerrm := sqlerrm;

rollback;
out_Flag := 999;

end;

<<NoXueXiPlan>>
if (out_Flag is null) then
out_Flag := 101; -- 没找到对应的学习计划
out_sqlerrm := '没找到对应的学习计划。';
end if;

<<TheCourceIsOver>>
if (out_Flag is null) then
out_Flag := 103; -- 当前课程已结束
out_sqlerrm := '该课程已结束，请确认。';
end if;

<<CantQianDao>>
if (out_Flag is null) then
out_Flag := 104; -- 未到签到时间
out_sqlerrm := '该课程尚未开始签到，请确认。';
end if;

<<PayFirst>>
if (out_Flag is null) then
out_Flag := 105; -- 有未收款金额
out_sqlerrm := '未收款金额：￥' || to_char(out_unpayed_amount) || '元';
end if;

<<TheQrCodeAlreadySigned>>
if (out_Flag is null) then
out_Flag := 106; -- 此二维码已经签到过
out_sqlerrm := '此二维码已经签到过，请确认。';
end if;

<<TheQrCodeTypeIsnotCanjia>>
if (out_Flag is null) then
out_Flag := 108; -- 此签到表的参课判定不是参加
out_sqlerrm := '此签到表的参课判定不是参加，请确认。';
end if;

<<TheQrCodeTypeIsZhengShi>>
if (out_Flag is null) then
out_Flag := 107; -- 此签到表的类型为正式课
out_sqlerrm := '此签到表的类型为正式课，请确认。';
end if;

<<InvalidQrCode>>
if (out_Flag is null) then
out_Flag := 102; -- 非法的签到二维码
out_sqlerrm := '不可识别的签到二维码，请确认。';
end if;

end;

ESI_GETLEARNPLAN

create or replace procedure ESI_GetLearnPlan(
          in_phone_number in varchar2, 
          in_company_name in varchar2, 
          in_status in int,  
          out_learnPlanId out int,        --学习计划ID
          out_course_name out varchar2,   --课程名称
          out_status out int,             --0：未开课、1：已开课、2：全部
          out_start_date out varchar2,    --课程开始日期
          out_end_date out varchar2,      --课程结束日期
          out_students out varchar2,      --已签到学员姓名数组
          out_plan_count out int,         --签到人数
          out_is_confirm out int ,    --是否确认 （true或false）
          out_is_manager out boolean  --是否为确认人
          ) as 
          tc_account_id       varchar2(22); --客户Id     
begin
  


--获取客户Id

select a.account_id into tc_account_id  from tc_account a left join tc_account_attr b on a.account_id=b.account_id
where a.org_id=1 and a.org_id=b.org_id and a.account_id=b.account_id
and a.account_name= in_company_name
and (
a.account_mobile_phone = in_phone_number
or a.account_phone=in_phone_number
or b.acct_char14= in_phone_number

or exists(
select 1 from tc_contact c where c.org_id=1 and (c.mobile= in_phone_number or c.cnct_char03= in_phone_number) and c.account_id=a.account_id
)
);

/*if tc_account_id = null then   
tc_account_id := 0; 
end if;
*/
--根据客户取学习计划
select l.learnplan_id ,                     --学习计划ID
       l.learnplan_int01 ,                  --是否确认 （true或false）
       l.learnplan_dec04 ,                  --签到人数       
       l.learnplan_start_date,              --开始时间
       l.learnplan_end_date,                --结束日期 
       l.learnplan_name,                    --课程名称
       
       CASE WHEN in_status=0 then 1 else   l.learnplan_stage                    --状态0：未开课、1：已开课、2：全部
      into out_learnPlanId ,out_is_confirm , out_plan_count ,out_start_date, out_end_date ,out_course_name,out_status
      from tcu_learnplan l
      where l.org_id=1 
      and l.account_id=tc_account_id
      and l.learnplan_type='2151';             --正式学习计划




  
 if in_status=0 then --未开课 加下面条件


 and  if then
      exists(
      select 1 from tcu_jiaoxplan j where j.org_id=1 and  j.jiaoxplan_date01 > sysdate  and j.jiaoxplan_id=a.learnplan_refid02
      )     
      end if



end if

--去签到表里获取











end ESI_GetLearnPlan;

ESI_MARKSTATE

CREATE OR REPLACE PROCEDURE "ESI_MARKSTATE" (in_QRCode   in varchar2,
                                          in_State    in int,
                                          out_Flag    out number,
                                          out_sqlcode out int,
                                          out_sqlerrm out varchar2) as
  /*
       范  围：和微众对接
       作  用：更新迈向签到课程的“有效到场/合格总裁 "1001：是；1002：否；1003：无名片"”
     发生时间：微众调用我们更新签到接口后
     最后更新：2015-05-08 pm
  */
  the_State    int;
  out_the_Flag int;
begin

  if in_State = 0 then
    the_State := 1001;
  elsif in_State = 1 then
    the_State := 1002;
  elsif in_State = 2 then
    the_State := 1003;
  end if;

  begin
  
    select count(yckb_id)
      into out_the_Flag
      from tcu_yckb
     where yckb_int03 = 1001 -- 已签到
       and qrcode = in_QRCode;
  
    if out_the_Flag = 0 then
      out_the_Flag := null;
      goto InvalidQrCode;
    end if;
  
    update tcu_yckb set yckb_int06 = the_State where qrcode = in_QRCode;
  
    out_Flag := 100;
    commit;
  exception
    when others then
      out_sqlcode := sqlcode;
    
      out_sqlerrm := sqlerrm;
    
      rollback;
      out_Flag := 999;
    
  end;

  <<InvalidQrCode>>
  if (out_Flag is null) then
    out_Flag    := 105; -- 非法的签到二维码
    out_sqlerrm := '不可识别的签到二维码，请确认。';
  end if;

end;