Setup subscriptionrules

Hi,

I want to add subscription based invoicing to my application. The invoicing itself is not part of this question as that is already in place.

As multiple customers will use this functionaity I want to setup subscriptionrules in a very flexible way. The biggest challange for me is to allow different periods (intervals) for each rule (one per rule). F.e. when you look at recurring appointments in MS Outlook (see attached image - Dutch) you get an idea what I'm aiming for.Patterns-of-recurrence.png
My challange is how to efficiently store all these settings into a table (datamodal) and to determine which rule(s) need to be invoiced based on the current date. After a rule is invoiced the last-invoiced-date will be set for the rule.
  • How to store these subscriptionrules efficiently in a database?
  • How to determine which rule(s) must be executed (invoiced) based on a given day?
  • How to make sure a rule is never skipped (f.e. when application isn't used for some days/weeks))
If a rule should have been executed a few times (based on last-invoiced-date, period and current date) all 'missed' executions must be all executed anyhow afterwards.

Any simple source-examples are very welcome as is any advice on the issue at hand.
Thanks!
Stef MerlijnDeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Stef MerlijnConnect With a Mentor DeveloperAuthor Commented:
I found a solution at following link:
https://github.com/eugeniomiro/TestScheduledJobs
This has all the basic functionality I need.
0
 
Russell FoxConnect With a Mentor Database DeveloperCommented:
Hi Delphiwizard, since you're using SQL Server you should take a look at the Agent scheduling in SQL. It has the same flexibility you're looking for and you should be able to use the user's input to create an Agent job that runs the invoicing procedure on schedule, regardless of whether or not they use the application that day. Below is the syntax for the sp_add_schedule internal store procedure, so you can see how Microsoft stores this info (and you can then use sp_add_schedule to create your user's schedule):
sp_add_schedule [ @schedule_name = ] 'schedule_name'   
    [ , [ @enabled = ] enabled ]  
    [ , [ @freq_type = ] freq_type ]  
    [ , [ @freq_interval = ] freq_interval ]   
    [ , [ @freq_subday_type = ] freq_subday_type ]   
    [ , [ @freq_subday_interval = ] freq_subday_interval ]   
    [ , [ @freq_relative_interval = ] freq_relative_interval ]   
    [ , [ @freq_recurrence_factor = ] freq_recurrence_factor ]   
    [ , [ @active_start_date = ] active_start_date ]   
    [ , [ @active_end_date = ] active_end_date ]   
    [ , [ @active_start_time = ] active_start_time ]   
    [ , [ @active_end_time = ] active_end_time ]   
    [ , [ @owner_login_name = ] 'owner_login_name' ]  
    [ , [ @schedule_uid = ] schedule_uid OUTPUT ]  
    [ , [ @schedule_id = ] schedule_id OUTPUT ]  
    [ , [ @originating_server = ] server_name ] /* internal */  

Open in new window


Hope it helps!
0
 
Stef MerlijnDeveloperAuthor Commented:
@Russell Fox: Your idea is definitely usefull, but my application including SQL Server are frequently installed on a stand-alone desktop/laptop which is shutdown add the end of the day and might not be used for a few days or more. Therefor I prefer to store settings in a table (also for easy access and maintenance of rules by the enduser).

By the way I use SQL Server 2014 Express edition where SQL Server Agent is not available.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Geert GConnect With a Mentor Oracle dbaCommented:
in oracle 12.1 i have scheduling groups as a set of recurring items

CREATE TABLE SCHED
(
  GROUPID                 INTEGER               NOT NULL,
  ID                      INTEGER               DEFAULT "S_SCHED"."NEXTVAL" NOT NULL,
  INTERVAL_DAYS           NUMBER,
  DT_ONETIME              DATE,
  WEEKDAYNR               INTEGER,
  MONTHDAYNR              INTEGER,
  STARTTIME               VARCHAR2(20 CHAR),
  INTERVAL_DURATION_DAYS  NUMBER
);

CREATE TABLE SCHEDGROUP
(
  GROUPID  INTEGER                              DEFAULT "S_SCHEDGROUP"."NEXTVAL" NOT NULL,
  NAME     VARCHAR2(50 CHAR),
  DESCR    VARCHAR2(500 CHAR),
  ENABLED  INTEGER                              DEFAULT 1
);

ALTER TABLE REFMAN.SCHED ADD (
  CONSTRAINT FK_SCHED_SCHEDGROUP 
  FOREIGN KEY (GROUPID) 
  REFERENCES REFMAN.SCHEDGROUP (GROUPID)
  ENABLE VALIDATE);

Open in new window


and a function to evaluate for the occurence of the schedule based on a specific start time
CREATE OR REPLACE function next_date(ischedgroupid in integer, ifromdate in date default sysdate) return date
is
  vnextdate date;
  vfirstdate date;
  procedure evaldates is  
  begin
    if vnextdate is not null and (vfirstdate is null or vnextdate < vfirstdate) then 
      vfirstdate := vnextdate;
    end if;  
  end; 
begin
  vnextdate := null;
  vfirstdate := null;
  
  -- one time only events
  begin 
    select dt_onetime into vnextdate
    from schedgroup sg, sched s
    where sg.groupid = s.groupid
      and sg.groupid = ischedgroupid
      and s.dt_onetime > ifromdate
      and s.dt_onetime is not null
    order by s.dt_onetime asc      
    fetch first 1 row only;
  exception
    when NO_DATA_FOUND then 
      null;
  end;
  evaldates;
  
  --specific starting time in day of week or day of month
  begin
    select
      dt into vnextdate 
    from (
      select  
        to_date(to_char(      
          case 
            when s.weekdaynr is not null then trunc(ifromdate, 'IW') + s.weekdaynr -1
            else trunc(ifromdate, 'MM') + s.monthdaynr -1
          end, 'dd/mm/yyyy') ||' '||s.starttime, 'dd/mm/yyyy hh24:mi:ss') dt 
      from schedgroup sg, sched s
      where sg.groupid = s.groupid
        and sg.groupid = ischedgroupid
        and s.interval_days is null
        and s.starttime is not null
        and (s.weekdaynr is not null or s.monthdaynr is not null)
        and  to_date(to_char(      
          case 
            when s.weekdaynr is not null then trunc(ifromdate, 'IW') + s.weekdaynr -1
            else trunc(ifromdate, 'MM') + s.monthdaynr -1
          end, 'dd/mm/yyyy') ||' '||s.starttime, 'dd/mm/yyyy hh24:mi:ss') > ifromdate)
    order by 1
    fetch first 1 row only; 
  exception 
    when no_data_found then 
      null;
  end;
  evaldates;
  
  -- interval with duration for specific day or specific month day
  begin
    with 
      dttemp as (
        select
          s.id,  
          case 
            when s.weekdaynr is not null then trunc(ifromdate, 'IW') + s.weekdaynr -1
            else trunc(ifromdate, 'MM') + s.monthdaynr -1
          end dt_day_start,
          s.starttime,
          s.interval_days,
          s.interval_duration_days
        from schedgroup sg, sched s
        where sg.groupid = s.groupid
          and sg.groupid = ischedgroupid
          and s.interval_days is not null
          and s.starttime is not null
          and (s.weekdaynr is not null or s.monthdaynr is not null)),
      dtrange as (          
        select 
          id, 
          to_date(to_char(dt_day_start, 'dd/mm/yyyy')||' '||starttime, 'dd/mm/yyyy hh24:mi:ss') dt_start,
          interval_days,
          decode(interval_duration_days, 
            null, dt_day_start+1, 
            to_date(to_char(dt_day_start, 'dd/mm/yyyy')||' '||starttime, 'dd/mm/yyyy hh24:mi:ss') + interval_duration_days) dt_end  
        from dttemp),
      dtrecs as (
        select level nr  
        from dual connect by level < 1000)
    select r.dt_start + r.interval_days*(dr.nr-1) into vnextdate
    from dtrange r, dtrecs dr
    where r.dt_start + r.interval_days*(dr.nr-1) > ifromdate
      and r.dt_start + r.interval_days*(dr.nr-1) <= r.dt_end
    order by 1
    fetch first 1 row only;
  exception 
    when no_data_found then 
      null;
  end;
  evaldates;
  
  return vfirstdate; 
end;
/

Open in new window


typically i use the last occurrence of the event to calculate the next occurrence
this way i can calculate in the past and in the future

i think you'll have a job on to convert this to mssql
it's using hierarchical recursive subquery factoring
a temp table might help to solve that
0
 
Stef MerlijnDeveloperAuthor Commented:
Thank you Geert, I'll have a look at it.
0
 
Stef MerlijnDeveloperAuthor Commented:
Thank you for your advice and pointing me in the right direction.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.