Solved

Setup subscriptionrules

Posted on 2016-07-18
6
48 Views
Last Modified: 2016-07-25
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!
0
Comment
Question by:Delphiwizard
  • 4
6 Comments
 
LVL 13

Assisted Solution

by:Russell Fox
Russell Fox earned 250 total points
ID: 41718069
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
 

Author Comment

by:Delphiwizard
ID: 41718153
@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
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 250 total points
ID: 41719151
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly‚ÄĒanytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:Delphiwizard
ID: 41719374
Thank you Geert, I'll have a look at it.
0
 

Accepted Solution

by:
Delphiwizard earned 0 total points
ID: 41722199
I found a solution at following link:
https://github.com/eugeniomiro/TestScheduledJobs
This has all the basic functionality I need.
0
 

Author Closing Comment

by:Delphiwizard
ID: 41727379
Thank you for your advice and pointing me in the right direction.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now