pears.NetCandidateShiftTemplates

CREATE PROCEDURE "pears"."NetCandidateShiftTemplates"( IN "pWebUserID" CHAR(20),IN "CategoryFilter" CHAR(1) DEFAULT NULL ) 
RESULT( "EventID" CHAR(20),"EventName" CHAR(100),"EventFrom" CHAR(20),"EventTo" CHAR(20),"Grp" CHAR(1),"SortOrder" CHAR(10) ) 
BEGIN
  DECLARE "DivID" CHAR(20);
  DECLARE "DefaultFrom" CHAR(10) = '00:00';
  DECLARE "DefaultTo" CHAR(10) = '23:59';
  DECLARE "CatFilter" CHAR(1);
  SET "DivID" = (SELECT FIRST "divisionid" FROM "person" AS "p" KEY JOIN "iqxnetuserlink" AS "i" WHERE "i"."iqxnetuserid" = "pWebUserID");
  SELECT top 1 "dateformat"("p"."defaultshiftfrom",'hh:nn'),"dateformat"("defaultshiftto",'hh:nn') INTO "DefaultFrom","DefaultTo" FROM "params" AS "p";
  SELECT "AvailTemplateID" AS "EventID",
    "string"("Name",' ',"dateformat"("isnull"("TimeFrom","DefaultFrom"),'hh:nn'),' - ',"dateformat"("isnull"("TimeTo","DefaultTo"),'hh:nn')) AS "EventName",
    "dateformat"("isnull"("TimeFrom","DefaultFrom"),'hh:nn') AS "EventFrom",
    "dateformat"("isnull"("TimeTo","DefaultTo"),'hh:nn') AS "EventTo",
    'A' AS "Grp",
    "dateformat"("EventFrom",'hhnn') AS "SOrder"
    FROM "AvailabilityTemplate" AS "a"
    WHERE("a"."divisionid" = "DivID" OR "isnull"("a"."divisionid",'') = '')
    AND "PublishToWeb" = '1'
    AND "Grp" = "isnull"("CategoryFilter",'A') UNION
  SELECT "TempShiftUnavailableReasonID" AS "EventID",
    "Name" AS "EventName",
    "DefaultFrom" AS "EventFrom",
    "DefaultTo" AS "EventTo",
    'U' AS "Grp",
    "Sortorder" AS "SOrder"
    FROM "TempShiftUnavailableReason"
    WHERE "isnull"("PublishToWeb",'0') = '1'
    AND "Grp" = "isnull"("CategoryFilter",'U')
    ORDER BY "Grp" ASC,"SOrder" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateShiftTemplates" IS 
{CREATE PROCEDURE pears."NetCandidateShiftTemplates"(IN pWebUserID CHAR(20), IN CategoryFilter CHAR(1) DEFAULT NULL)
RESULT(EventID CHAR(20),EventName CHAR(100),EventFrom CHAR(20),EventTo CHAR(20),Grp CHAR(1), SortOrder CHAR(10))
BEGIN
	DECLARE DivID CHAR(20);
    DECLARE DefaultFrom CHAR(10) DEFAULT '00:00';
    DECLARE DefaultTo CHAR(10) DEFAULT '23:59';
    DECLARE CatFilter CHAR(1);
 
    SET DivID = (SELECT FIRST divisionid FROM person AS p KEY JOIN iqxnetuserlink AS i WHERE i.iqxnetuserid = pWebUserID);
    SELECT top 1 dateformat(p.defaultshiftfrom,'hh:nn'),dateformat(defaultshiftto,'hh:nn') INTO DefaultFrom,DefaultTo FROM params p;
 
    SELECT
        AvailTemplateID AS EventID, 
        string(Name,' ',dateformat(isnull(TimeFrom,DefaultFrom),'hh:nn'),' - ',dateformat(isnull(TimeTo,DefaultTo),'hh:nn')) AS EventName,
        dateformat(isnull(TimeFrom,DefaultFrom),'hh:nn') AS EventFrom,
        dateformat(isnull(TimeTo,DefaultTo),'hh:nn') AS EventTo,
        'A' AS Grp,
        dateformat(EventFrom,'hhnn') AS SOrder
    FROM 
        AvailabilityTemplate AS a
    WHERE
        (a.divisionid = DivID OR isnull(a.divisionid,'') = '') 
        AND PublishToWeb = '1'
        AND Grp = isnull(CategoryFilter,'A')
    UNION
    SELECT 
        TempShiftUnavailableReasonID AS EventID,
        Name AS EventName,
        DefaultFrom AS EventFrom,
        DefaultTo AS EventTo,
        'U' AS Grp,
        Sortorder AS SOrder    
    FROM
        TempShiftUnavailableReason
    WHERE 
        isnull(PublishToWeb,'0') = '1'
        AND Grp = isnull(CategoryFilter,'U')
    ORDER BY
        Grp ASC,SOrder ASC
 
END
}