Solved

Setup subscriptionrules

Posted on 2016-07-18
6
94 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 37

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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 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