CREATE OR REPLACE PROCEDURE "MYCAR"."ADDSOFTCONFREFSOFT"
(v_soft_id IN diag_soft.soft_id%TYPE,v_free_time IN diag_soft.free_time%TYPE, v_soft_conf_id IN sys_soft_conf.soft_conf_id%TYPE)
is
 cursor c is
  select p.serial_no,p.user_id,p.reg_time
  from sys_product p
  where p.soft_conf_id = v_soft_conf_id and p.pdt_state = 1
    and p.pdt_type_id = (select ss.pdt_type_id from diag_soft ss where ss.soft_id=v_soft_id );
    
  v_serial_no sys_product.serial_no%TYPE;
  v_user_id sys_product.user_id%TYPE;
  v_reg_time sys_product.reg_time%TYPE;
  v_count number;
  v_count1 number;
  v_conf_time sys_soft_conf.create_date%TYPE;
  v_conf_free_time sys_soft_conf.free_date%TYPE;
  v_max_free_time sys_soft_conf.free_date%TYPE; --比较两个免费日期最大的
  v_create_time diag_soft.create_time%TYPE;
 
begin
   open c;
   select c.create_date,c.free_date into v_conf_time,v_conf_free_time from sys_soft_confc where c.soft_conf_id = v_soft_conf_id;

   select s.create_time into v_create_time from diag_soft s
   where s.soft_id = v_soft_id;
   loop
      fetch c into v_serial_no,v_user_id,v_reg_time;
       if c%FOUND then
            select count(*) into v_count
            from user_order uo,order_detail_info od
            where uo.order_id = od.order_id and od.soft_id = v_soft_id and uo.user_id = v_user_id
              and uo.order_status in (1,3);
             
            select count(*) into v_count1
            from diag_user_soft_center d
            where d.soft_id = v_soft_id and d.serial_no = v_serial_no;

            if(v_count1=0) then
              if(v_count > 0) then
                if(v_conf_free_time > v_free_time) then
                  v_max_free_time := v_conf_free_time;
                  v_create_time := v_conf_time;
                else
                    v_max_free_time := v_free_time;
                end if;
               
                    update diag_user_soft_center
                    set free_end_time = add_months(v_reg_time,v_max_free_time),
                        update_time = sysdate, soft_conf_id = v_soft_conf_id
                    where soft_id = v_soft_id and user_id = v_user_id and serial_no = v_serial_no;
              else
                 insert into diag_user_soft_center(user_soft_id,soft_id,user_id,soft_type,
                                                   free_start_time,free_end_time,create_time,
                                                   update_time,serial_no,soft_conf_id)
                  values(SEQ_DIAG_USER_SOFT_CENTER.Nextval,v_soft_id,
                         v_user_id,1,v_reg_time,add_months(v_reg_time,v_conf_free_time),
                         sysdate,sysdate,v_serial_no,v_soft_conf_id);
              end if;
          end if;
        else
          exit;
        end if;
   end loop;
   commit;
   close c;
  
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_Output.put_line('增加软件配置失败');
        rollback;
end addsoftconfrefsoft;