pears.NetOwnerRegistrations

CREATE PROCEDURE "pears"."NetOwnerRegistrations"( IN "pWebUserID" CHAR(20),IN "pCandidateID" CHAR(20) DEFAULT NULL ) 
RESULT( "DepartmentID" CHAR(10),"PersonID" CHAR(20),"DepartmentName" CHAR(100),"TempJobs" SMALLINT,"PermJobs" SMALLINT,"Details" CHAR(100),"SortOrder" CHAR(20) ) 
// IQXWeb
BEGIN
  SELECT DISTINCT
    "department"."departmentid",
    "search"."personid",
    "department"."name" AS "DeptName",
    "isnull"("search"."temp",0) AS "TempJobs",
    "isnull"("search"."permanent",0) AS "PermJobs",
    "isnull"("string"((IF "TempJobs" <> 0 THEN 'Temp jobs' endif),(IF "TempJobs"*"Permjobs" <> 0 THEN ', ' endif),(IF "PermJobs" <> 0 THEN 'Perm jobs' endif)),'Not Registered') AS "details",
    "department"."sortorder" AS "SO"
    FROM "department"
      KEY JOIN "search"
    WHERE "search"."personid" = "pCandidateID"
    AND "department"."searchable" = 1
    AND "department"."publishtoweb" = 1
    AND("department"."divisionid" IS NULL OR "department"."divisionid" = any(SELECT "p"."divisionid" FROM "person" AS "p" WHERE "p"."personid" = "pCandidateID"))
    AND("department"."divisionid" IS NULL OR "department"."divisionid" = any(SELECT "DivisionID" FROM "DashboardStaffDivisions"()))
    ORDER BY "SO" ASC,
    "DeptName" ASC
END /* DOC
2018-10-05 GJ Created Proc
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerRegistrations" IS 
{CREATE PROCEDURE pears."NetOwnerRegistrations"( IN pWebUserID CHAR(20), IN pCandidateID CHAR(20) DEFAULT NULL ) 
RESULT( DepartmentID CHAR(10),PersonID CHAR(20),DepartmentName CHAR(100),TempJobs SMALLINT,PermJobs SMALLINT,Details CHAR(100),SortOrder CHAR(20)) 
// IQXWeb
BEGIN
SELECT DISTINCT
    department.departmentid,
    [SEARCH].personid,
    department.name AS DeptName,
    isnull([SEARCH].[temp],0) AS TempJobs,
    isnull([SEARCH].permanent,0) AS PermJobs,
    isnull(string((IF TempJobs <> 0 THEN 'Temp jobs' endif ),(IF TempJobs * Permjobs <> 0 THEN ', ' endif ),(IF PermJobs <> 0 THEN 'Perm jobs' endif )),'Not Registered') AS details,
    department.sortorder AS SO
  FROM 
    department 
    KEY JOIN [SEARCH] 
  WHERE 
    [SEARCH].personid = pCandidateID 
    AND department.searchable = 1 
    AND department.publishtoweb = 1
    AND (department.divisionid IS NULL OR department.divisionid IN (SELECT p.divisionid FROM person p WHERE p.personid=pCandidateID)) 
    AND (department.divisionid IS NULL OR department.divisionid IN (SELECT DivisionID FROM DashboardStaffDivisions()))
  ORDER BY 
    SO ASC,
    DeptName ASC
END
/* DOC
2018-10-05 GJ Created Proc
*/
}