pears.NetOwnerDiary

CREATE PROCEDURE "pears"."NetOwnerDiary"( 
  IN "pWebUserID" CHAR(20),
  IN "pConsultantID" CHAR(20) DEFAULT NULL,
  IN "pStartDate" CHAR(30) DEFAULT NULL,
  IN "pEndDate" CHAR(30) DEFAULT NULL,
  IN "pSlice" INTEGER DEFAULT 0,
  IN "pSliceSize" INTEGER DEFAULT 1000 ) 
RESULT( 
  "DiaryID" CHAR(20),
  "Description" CHAR(100),
  "DiaryFrom" CHAR(30),
  "DiaryTo" CHAR(30),
  "Status" CHAR(1),
  "Priority" CHAR(1),
  "Notes" long VARCHAR,
  "Speil" CHAR(255),
  "PopupMinutes" CHAR(5),
  "ContactName" CHAR(60),
  "CandName" CHAR(60),
  "CompanyName" CHAR(60),
  "VacPos" CHAR(60),
  "CandID" CHAR(20),
  "VacID" CHAR(20),
  "ContactID" CHAR(20) ) 
// IQXWeb
BEGIN
  DECLARE "xStartDate" TIMESTAMP;
  DECLARE "xEndDate" TIMESTAMP;
  DECLARE "WhoseDiary" CHAR(20);
  DECLARE "x" INTEGER;
  DECLARE "y" INTEGER;
  SET "x" = "isNull"("pSliceSize",100);
  SET "y" = "x"*"isNull"("pSlice",0)+1;
  SET "xStartDate" = "isnull"("NetStringToTimestamp"("pStartDate"),CURRENT date-7);
  SET "xEndDate" = "isnull"("NetStringToTimestamp"("pEndDate"),CURRENT DATE+30);
  SET "WhoseDiary" = "isnull"("pConsultantID",(SELECT top 1 "staffid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID" ORDER BY "loginid" ASC));
  SELECT top "x" START at "y"
    "d"."diaryid" AS "DiaryID",
    "d"."description" AS "Description",
    "dateformat"("d"."diaryfrom",'dd/mm/yyyy HH:nn') AS "DiaryFrom",
    "dateformat"("d"."diaryto",'dd/mm/yyyy HH:nn') AS "DiaryTo",
    (IF "d"."DurationType" = 'R' THEN 'R' ELSE 'A' endif) AS "Status",
    "isnull"("d"."priority",0) AS "Priority",
    "d"."notes" AS "Notes",
    ((IF "isnull"("ContactName",'') <> '' THEN 'With '+"ContactName"+' of '+"CompanyName" endif)
    +(IF "isnull"("CandName",'') <> '' AND "isnull"("ContactName",'') <> '' THEN ' and ' endif)
    +(IF "isnull"("CandName",'') <> '' AND "isnull"("ContactName",'') = '' THEN 'With ' endif)
    +"CandName"
    +(IF "isnull"("VacPos",'') <> '' THEN ' regarding '+"VacPos" endif)) AS "Speil",
    "d"."PopupMinutes",
    "p"."name" AS "ContactName",
    "pe"."name" AS "CandName",
    "c"."name" AS "CompanyName",
    "v"."position" AS "VacPos",
    "d"."personid" AS "CandID",
    "d"."vacancyid" AS "VacID",
    "d"."employmentid" AS "ContactID"
    FROM "diary" AS "d"
      KEY JOIN "staff" AS "s"
      LEFT OUTER JOIN "iqxnetuser" AS "u"
      ,"diary" AS "d"
      LEFT OUTER JOIN "employment" AS "e"
      LEFT OUTER JOIN "person" AS "p"
      ,"employment" AS "e"
      LEFT OUTER JOIN "company" AS "c"
      ,"diary" AS "d"
      LEFT OUTER JOIN "person" AS "pe"
      ,"diary" AS "d"
      LEFT OUTER JOIN "vacancy" AS "v"
    WHERE "s"."staffid" = "WhoseDiary"
    AND "d"."diaryfrom" <= "xEndDate"
    AND "isNull"("d"."diaryto","d"."diaryfrom") >= "xStartDate"
    ORDER BY "d"."DiaryFrom" ASC
END /* DOC
2016-07-25 PC Provide master list for Owner diary
2016-09-09 PC GJ remove date filter, but keep it handy for the moment. Remember to re-edit the TEST section
2016-09-30 PC modify date format to match NetOwnerDiarySet
2018-02-15 PC OP-29 allow consultants to view other calendars
2018-03-15 PC modify method of selection of 'other' staff
2018-05-14 GJ Removed order by priority
2018-07-23 PC revise test routines
2018-08-01 PC modify date parameters and add pSlice
2018-08-03 ET modify date filter now uses a string input (e.g. 2018-02-02 20:10) which is converted to a time stamp.
 
Tests:
Create three records, 2 months in past, today and one month in future
should return default dataset limited to month start-7 and month end+7
should return dataset limited to one month in past and month end+7
should return dataset limited to three days in past and month end+7
should return dataset limited to three months in past and one month in future
should return dataset limited to between one month and three months in future
should return dataset limited to between today and one month in future
should return default dataset limited to month start-7 and month end+7, ordered by priority
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.owner';
insert into diary (diaryid,staffid,personid,vacancyid,employmentid,diaryfrom,diaryto,description,notes,whenentered,whoentered,priority) on existing update values ('TEST1','TEST','TEST','TEST','TEST',now(*),now(*),'TestEntry','TestEntryNotes',now(*),'TEST','5');
insert into diary (diaryid,staffid,personid,vacancyid,employmentid,diaryfrom,diaryto,description,notes,whenentered,whoentered,priority) on existing update values ('TEST2','TEST','TEST','TEST','TEST',dateadd(mm,-2,now(*)),dateadd(mm,-2,now(*)),'TestEntry2','TestEntryNotes2',now(*),'TEST','5');
insert into diary (diaryid,staffid,personid,vacancyid,employmentid,diaryfrom,diaryto,description,notes,whenentered,whoentered,priority) on existing update values ('TEST3','TEST','TEST','TEST','TEST',dateadd(mm,2,now(*)),dateadd(mm,2,now(*)),'TestEntry3','TestEntryNotes3',now(*),'TEST','5');
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,null,null) where diaryid like 'test%' order by diaryid;
expect DiaryID=TEST1,Description=TestEntry,Status=R,Priority=5,Notes=TestEntryNotes;
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,dateadd(mm,-1,now(*)),null) where diaryid like 'test%';
expect DiaryID=TEST1,Description=TestEntry,Status=R,Priority=5,Notes=TestEntryNotes;
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,dateadd(mm,-3,now(*)),null) where diaryid like 'test%';
expect DiaryID=TEST1,Description=TestEntry,Status=R,Priority=5,Notes=TestEntryNotes;
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,dateadd(mm,-3,now(*)),dateadd(mm,-1,now(*))) where diaryid like 'test%';
expect DiaryID=TEST1,Description=TestEntry,Status=R,Priority=5,Notes=TestEntryNotes;
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,dateadd(mm,1,now(*)),dateadd(mm,3,now(*))) where diaryid like 'test%';
expect DiaryID=TEST1,Description=TestEntry,Status=R,Priority=5,Notes=TestEntryNotes;
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,dateadd(mm,1,now(*))) where diaryid like 'test%';
insert into diary (diaryid,staffid,personid,vacancyid,employmentid,diaryfrom,diaryto,description,notes,whenentered,whoentered,priority) values ('TEST4','TEST','TEST','TEST','TEST',now(*),now(*),'TestEntry4','TestEntryNotes4',now(*),'TEST','1');
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,null,null) where diaryid like 'test%' order by DiaryID;
expect DiaryID=TEST1,Description=TestEntry,Status=R,Priority=5,Notes=TestEntryNotes;
expect DiaryID=TEST4,Description=TestEntry4,Status=R,Priority=1,Notes=TestEntryNotes4;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerDiary" IS 
{CREATE PROCEDURE pears."NetOwnerDiary"(
    IN pWebUserID CHAR(20),
    IN pConsultantID CHAR(20) DEFAULT NULL,
    IN pStartDate CHAR(30) DEFAULT NULL,
    IN pEndDate CHAR(30) DEFAULT NULL,
    IN pSlice INTEGER DEFAULT 0,
    IN pSliceSize INTEGER DEFAULT 1000
)
RESULT(
    DiaryID CHAR(20),
    Description CHAR(100),
    DiaryFrom CHAR(30),
    DiaryTo CHAR(30),
    STATUS CHAR(1),
    Priority CHAR(1),
    Notes long VARCHAR,
    Speil CHAR(255),
    PopupMinutes CHAR(5),
    ContactName CHAR(60),
    CandName CHAR(60),
    CompanyName CHAR(60),
    VacPos CHAR(60),
    CandID CHAR(20),
    VacID CHAR(20),
    ContactID CHAR(20)
)
// IQXWeb
BEGIN
  DECLARE xStartDate TIMESTAMP;
  DECLARE xEndDate TIMESTAMP;
  DECLARE WhoseDiary CHAR(20);
  DECLARE x INTEGER;
  DECLARE y INTEGER;
 
  SET x=isNull(pSliceSize, 100);
  SET y=x*isNull(pSlice, 0)+1;
 
  SET xStartDate=isnull(NetStringToTimestamp(pStartDate), CURRENT DATE - 7);
  SET xEndDate=isnull(NetStringToTimestamp(pEndDate), CURRENT DATE + 30);
  SET WhoseDiary=isnull(pConsultantID,(SELECT top 1 staffid FROM iqxnetuser WHERE iqxnetuserid=pWebUserID ORDER BY loginid));
SELECT top x START at y
   d.diaryid AS DiaryID,
   d.description AS Description,
   dateformat(d.diaryfrom,'dd/mm/yyyy HH:nn') AS DiaryFrom,
   dateformat(d.diaryto,'dd/mm/yyyy HH:nn') AS DiaryTo,
   (IF d.DurationType = 'R' THEN 'R' ELSE 'A' endif) AS STATUS,
   isnull(d.priority,0) AS Priority,
   d.notes AS Notes,
   ((IF isnull(ContactName,'')!='' THEN 'With ' + ContactName + ' of ' + CompanyName endif) +
   (IF isnull(CandName,'')!='' AND isnull(ContactName,'')!='' THEN ' and ' endif) +
   (IF isnull(CandName,'')!='' AND isnull(ContactName,'')='' THEN 'With ' endif) +
   CandName +
   (IF isnull(VacPos,'')!='' THEN ' regarding ' + VacPos endif)) AS Speil,
   d.PopupMinutes,
   p.name AS ContactName,
   pe.name AS CandName,
   c.name AS CompanyName,
   v.position AS VacPos,
   d.personid AS CandID,
   d.vacancyid AS VacID,
   d.employmentid AS ContactID
FROM
   diary d
   KEY JOIN staff s
   LEFT JOIN iqxnetuser u,
   diary d
   LEFT JOIN employment e
   LEFT JOIN person p,
   employment e
   LEFT JOIN company c,
   diary d
   LEFT JOIN person pe,
   diary d
   LEFT JOIN vacancy v
WHERE
   s.staffid=WhoseDiary
   AND d.diaryfrom <= xEndDate
   AND isNull(d.diaryto, d.diaryfrom) >= xStartDate
ORDER BY
   d.DiaryFrom
END
 
/* DOC
2016-07-25 PC Provide master list for Owner diary
2016-09-09 PC GJ remove date filter, but keep it handy for the moment. Remember to re-edit the TEST section
2016-09-30 PC modify date format to match NetOwnerDiarySet
2018-02-15 PC OP-29 allow consultants to view other calendars
2018-03-15 PC modify method of selection of 'other' staff
2018-05-14 GJ Removed order by priority
2018-07-23 PC revise test routines
2018-08-01 PC modify date parameters and add pSlice
2018-08-03 ET modify date filter now uses a string input (e.g. 2018-02-02 20:10) which is converted to a time stamp.
 
Tests:
    Create three records, 2 months in past, today and one month in future
    should return default dataset limited to month start-7 and month end+7
    should return dataset limited to one month in past and month end+7
    should return dataset limited to three days in past and month end+7
    should return dataset limited to three months in past and one month in future
    should return dataset limited to between one month and three months in future
    should return dataset limited to between today and one month in future
    should return default dataset limited to month start-7 and month end+7, ordered by priority
*/
 
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.owner';
insert into diary (diaryid,staffid,personid,vacancyid,employmentid,diaryfrom,diaryto,description,notes,whenentered,whoentered,priority) on existing update values ('TEST1','TEST','TEST','TEST','TEST',now(*),now(*),'TestEntry','TestEntryNotes',now(*),'TEST','5');
insert into diary (diaryid,staffid,personid,vacancyid,employmentid,diaryfrom,diaryto,description,notes,whenentered,whoentered,priority) on existing update values ('TEST2','TEST','TEST','TEST','TEST',dateadd(mm,-2,now(*)),dateadd(mm,-2,now(*)),'TestEntry2','TestEntryNotes2',now(*),'TEST','5');
insert into diary (diaryid,staffid,personid,vacancyid,employmentid,diaryfrom,diaryto,description,notes,whenentered,whoentered,priority) on existing update values ('TEST3','TEST','TEST','TEST','TEST',dateadd(mm,2,now(*)),dateadd(mm,2,now(*)),'TestEntry3','TestEntryNotes3',now(*),'TEST','5');
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,null,null) where diaryid like 'test%' order by diaryid;
expect DiaryID=TEST1,Description=TestEntry,Status=R,Priority=5,Notes=TestEntryNotes;
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,dateadd(mm,-1,now(*)),null) where diaryid like 'test%';
expect DiaryID=TEST1,Description=TestEntry,Status=R,Priority=5,Notes=TestEntryNotes;
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,dateadd(mm,-3,now(*)),null) where diaryid like 'test%';
expect DiaryID=TEST1,Description=TestEntry,Status=R,Priority=5,Notes=TestEntryNotes;
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,dateadd(mm,-3,now(*)),dateadd(mm,-1,now(*))) where diaryid like 'test%';
expect DiaryID=TEST1,Description=TestEntry,Status=R,Priority=5,Notes=TestEntryNotes;
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,dateadd(mm,1,now(*)),dateadd(mm,3,now(*))) where diaryid like 'test%';
expect DiaryID=TEST1,Description=TestEntry,Status=R,Priority=5,Notes=TestEntryNotes;
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,dateadd(mm,1,now(*))) where diaryid like 'test%';
insert into diary (diaryid,staffid,personid,vacancyid,employmentid,diaryfrom,diaryto,description,notes,whenentered,whoentered,priority) values ('TEST4','TEST','TEST','TEST','TEST',now(*),now(*),'TestEntry4','TestEntryNotes4',now(*),'TEST','1');
select DiaryID,Description,Status,Priority,Notes from NetOwnerDiary('TEST.OWNER',null,null,null) where diaryid like 'test%' order by DiaryID;
expect DiaryID=TEST1,Description=TestEntry,Status=R,Priority=5,Notes=TestEntryNotes;
expect DiaryID=TEST4,Description=TestEntry4,Status=R,Priority=1,Notes=TestEntryNotes4;
*/
}